Help Fixing Coloring Macro

[Tysone]

Here is my Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "Y" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")) _
..Interior.Color = RGB(204, 255, 204)
Else
Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")) _
..Interior.Color = RGB(255, 255, 255)
End If
End If
End Sub

Three things I would like to adjust on it.

1) I would like this to only effect rows 23 and lower

2) I would like the "Y" criteria not to be case sensitive

3) The Else is set to 255, 255, 255 (white) but is there a setting for
just no color at all?


Thanks for any help,


Tyson

 

[Tom Ogilvy]

By 23 and lower do you mean 23, 24, 25 etc. That is my assumption.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then.
If Target.Cells.Count > 1 or Target.row < 23 Then Exit Sub
If Ucase(Target.Value) = "Y" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")) _
.Interior.Color = RGB(204, 255, 204)
Else
Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")) _
.Interior.ColorIndex = xlNone
End If
End If
End Sub

 

[Tysone]

Thanks Tom, just what I needed.

Tyson