colour row

[Trevor3007]

I have worksheet A2:L50 which gets updated regular. When I insert ‘Yes’ in cell k2, I would like the ‘whole row (a2:l2) to fill colour to light blue.


Would some kind person be able to help & sort for me?

MTIA
Trevor3007

 

[gallen]

Hello, this does as you ask

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("K2").Address Then
        If UCase(Target) = "YES" Then
            Range("A2:L2").Interior.ColorIndex = 33
        Else
            Range("A2:L2").Interior.ColorIndex = [COLOR=#303336][FONT=inherit]xlNone [/FONT][/COLOR]
        End If
    End If
End Sub

 

[Peter_SSs]

1. Select from A2 to L50
2. Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =$L2="Yes" -> Format... -> Fill tab -> Choose your colour -> OK -> OK

 

[Peter_SSs]

If you did want a vba solution, then I think gallen's Worksheet_Change code needs some tweaks to be robust and do what you want. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, Cell As Range
  
  Set Changed = Intersect(Target, Range("L2:L50"))
  If Not Changed Is Nothing Then
    For Each Cell In Changed
      If UCase(Cell.Value) = "YES" Then
        Rows(Cell.Row).Resize(, Cell.Column).Interior.ColorIndex = 33
      Else
        Rows(Cell.Row).Resize(, Cell.Column).Interior.ColorIndex = xlNone
      End If
    Next Cell
  End If
End Sub