Overwrite conditional formatting

[Gordon]

Hi...

I want a user to place a 'Y' in a cell that will turn the font of the entire
active row grey font on a white background regardless of any conditional
formatting in place. I'm struggling - any help appreciated.

 

[Gary''s Student]

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1")
Set r2 = Range("A1:IV1")
If Intersect(Target, r) Is Nothing Then Exit Sub
If r.Value <> "Y" Then Exit Sub
Application.EnableEvents = False
r2.ClearFormats
r2.Interior.ColorIndex = xlNone
r2.Font.ColorIndex = 48
Application.EnableEvents = True
End Sub

This is worksheet event code it looks at cell A1.

 

[Gordon]

Thanks for this. What I need is for any cell to accpet the Y to work on any
active row?

 

[Gary''s Student]

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Target
Set r2 = Target.EntireRow
If r.Value <> "Y" Then Exit Sub
Application.EnableEvents = False
r2.ClearFormats
r2.Interior.ColorIndex = xlNone
r2.Font.ColorIndex = 48
Application.EnableEvents = True
End Sub