CODEWITHSUNDEEP
excelcellvba
mark jay
mark jay

Reputation: 19

Excel: Run A Macro On Cell Click

I am using the following code to insert todays date into a cell when a user clicks onto another cell in that row.

At the moment the code is set out like so; if a user clicks cell AQ8 then insert date into cell AS8.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address = "$AQ$8" Then
       Range("AS8").Value = DATE
    End If
End Sub

however, now i want to change this code slightly so that when a user changes any AQ cell in either row then the subsequent AS cell has the date inserted into the appropriate cell for that row. So if cell AQ9 is clicked then AS9 shows todays date and when they click AQ10 then AS10 shows todays date and so on. Would this be written as a target row function? and if so how would i write something like this? Can someone please show me how i could get this to do what i need, thanks in advance

Upvotes: 1

Views: 28325

Answers (2)

djikay
djikay

Reputation: 10648

If you don't want to refer to columns by number, i.e. you want to use "AQ" instead of 43, then the following should work:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = Range("AQ1").Column Then
    Range("AS" & Target.Row).Value = Date
  End If
End Sub

The reference to row 1 in AQ1 is a dummy, since the code just selects the .Column of that range.

I'm using Worksheet_Change because you said "when a user changes any AQ cell". If you only want this to happen when a user clicks on a cell, then you can keep using the Worksheet_SelectionChange event.

Upvotes: 1

user3514930
user3514930

Reputation: 1717

Update like that:

If Target.Column = 43 Then
   Range("AS" & Target.Row).Value = Date
End If

every rows in column AQ = 43 are updated with the date.

Upvotes: 0

Related Questions