|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Please add example workbooks to save us wasting time recreating them
The below should work as a stand alone Macro, not done it as selection change Code:
Sub CheckDate() Dim TheDate As Date TheDate = Date If ActiveCell.Value = "" And ActiveCell.Offset(-1, 0) = TheDate Then Range("A" & ActiveCell.Row) = TheDate End If End Sub |
#3
|
|||
|
|||
Thank you for taking the time to respond to my post and upload some code. I apologize for not providing a document to work with. In addition, re-reading my post, I see I wasn't terribly clear about the issue.
This lack of clarity hints at the problem: I was tired. I miscounted the columns, the offset value was therefore incorrect, and the macro instead tried to activate a cell with a formula in it, which wouldn't activate, for some reason. I've adjusted the offset and the macro now works. Thanks again for your reply. The added lesson here for me is, always upload a file. |
#4
|
|||
|
|||
Glad it helped
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cell equal value new value of active cell which is a drop down | hassanmoukadem | Excel Programming | 1 | 04-13-2020 07:54 PM |
Active cell not the one I select with curser. | hayesfam3 | Excel | 0 | 08-15-2019 05:14 AM |
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow | FUGMAN | Excel Programming | 7 | 02-05-2017 08:37 AM |
Change Macro so it does not refer to "Active Document" | trent.bourne | Word VBA | 2 | 10-30-2016 01:49 AM |
Change cell color when selection is made from a drop down list | fedcco | Excel | 12 | 08-28-2012 10:43 PM |