View Single Post
 
Old 03-06-2021, 09:20 PM
Peterson Peterson is offline Windows 10 Office 2019
Competent Performer
 
Join Date: Jan 2017
Posts: 133
Peterson is on a distinguished road
Default Change active cell after running macro in active cell via selection change

I have a macro for a particular worksheet that adds today's date to Column A when the active cell is blank and the cell above it is also today's date. After the date is added, I'd like the active cell to be offset to another column.

I've been unable to activate the cell -- what am I doing wrong here?

Code:
Private Sub Worksheet_SelectionChange2(ByVal Target As Range) ' 03/06/2021
' This macro adds today's date in Column A if the cell is blank and the date
' in the cell above it is today.

    If Target.Column = 1 Then
        If ActiveCell = "" And ActiveCell.Offset(rowOffset:=-1, columnOffset:=0) = Date Then
            ActiveCell = Date
        End If
        
        ' This doesn't work, nor do a number of other approaches I've tried...
        ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate
    End If
End Sub
Reply With Quote