Change event for multiple rows
 

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("A7:P7").Select

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

End If

End Sub

Change event for multiple rows
 

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


"Thanks" wrote in message
...
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("A7:P7").Select

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

End If

End Sub