Changing cell colors as determined by a variable

[xxsacra]

The titles a bit shaky, sorry if it makes no sense

Anyway, im trying to write a code (im pretty nood at excel so bare with me here) that when i put 'Y' (as in yes) into column F it fills that whole row with red or orange. thae code im using at the moment seems to have some end if issues.
it could just be a terrible code.

tl;dr When i put 'Y' into column x i want it to fill the row with red.

the (non working) code im using right now is

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If (Range("F:F").Value) = "Y" Then
            
            Rows("Target.Offset(0, 1)").Select
            With Selection.Interior
        .ColorIndex = 46
        .Pattern = xlSolid
End If
End With
End Sub

 

[Smitty]

Welcome to the Board!

Why not just use Conditional Formatting?

Select your range and then Data-->Conditional Formatting-->Formula is-->=$F1="Y"-->Format as desired.

As for your code, noting that it's not necessary, your End If/End With statements are transposed (among other things).

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 6) And Target.Value = "Y" Then
        With Rows(Target.Row).EntireRow.Interior
            .ColorIndex = 46
            .Pattern = xlSolid
        End With
    End If
End Sub

HTH,

 

[Norie]

This could be done without code using conditional formatting.

Is that an option?

By the way you mentioned 2 formats but only one criteria.

 

[xxsacra]

I couldn't quite figure out the formatting (I could make one cell orange but not the whole row) But that code works like a charm now (thanks smitty!!)

I really appreciate it guys.

This forum needs a +rep function :D

 

[Smitty]

No problem. Just note that it's generally wiser to go with a native solution vs. code. In this case Conditional Formatting will work just fine.