Solved: need dynamic range

[ndendrinos]

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B1") = "y" Then Range("A1").Font.ColorIndex = 3
If Range("B1") = "n" Then Range("A1").Font.ColorIndex = -4105
End Sub

Based on the idea above I want to use 4 columns like this:
Column A 's header is : Item
Column's B header is : Value
Column's C header is Part : Number
Column's D header is choose : Y (for Yes)/ N (for No)

If I choose Y then in the three adjacent cells to the left I need to change the font color to RED (3)
If I choose N then in the three adjacent cells to the left I need to change the font color to Automatic (-4105)

The reason for the N option is to correct an eventual mistake of choosing Y instead of N (hence reverting to Automatic)

The code needs to be "dynamic" and work as the user adds new "items"

Thank you

[macropod]

Hi ndendrinos,

Try:[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D")) Is Nothing Then Exit Sub
Select Case UCase(Intersect(Target, Range("D")))
Case "Y"
Target.Offset(0, -1).Font.ColorIndex = 3
Target.Offset(0, -2).Font.ColorIndex = 3
Target.Offset(0, -3).Font.ColorIndex = 3
Case "N"
Target.Offset(0, -1).Font.ColorIndex = -4105
Target.Offset(0, -2).Font.ColorIndex = -4105
Target.Offset(0, -3).Font.ColorIndex = -4105
End Select
End Sub[/VBA]

[mikerickson]

Have you tried ConditionalFormatting on A2:C2 with

=($D2="y")

and downwards.

[ndendrinos]

Hello Mike, Yes I did consider cond formatting but this time a worksheet change event is called for, Thank you.

Paul, exactly what I need. I did try with "cases" but did not think of using three lines (one for each columns) as you did. Excellent, Thank you too.