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

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" <(E-Mail Removed)> wrote in message
news:B4022200-7EB8-4B9E-8348-(E-Mail Removed)...
>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