Change event for multiple rows

[Thanks]

I have a spreadsheet with 100's of rows. As dates are changed on each row I
would like a specific range in that row to change colors accordingly. How do
I make a specific column range a variable, so that if a cell changes in that
column, it then makes that row change colors of the cell changed. Here is my
code for a specific row.

Private Sub Worksheet_Change(ByVal Target As Range)

mycell = Range("m7")
If IsDate(mycell) Then

Range("A77").Select

With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Else
Range("A77").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With

End If

End Sub

 

[Bernie Deitrick]

Thanks,

If you want this to work only one cell at a time:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> Range("m7").Column Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

With Cells(Target.Row, 1).Resize(1, 16).Interior
If IsDate(Target) Then
.ColorIndex = 15
.Pattern = xlSolid
Else
.ColorIndex = 34
.Pattern = xlSolid
End If
End With

End Sub


For multiple cells, you would need to loop or check the date of Target.Cells(1)

HTH,
Bernie
MS Excel MVP