colour row

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
667
Office Version
  1. 365
Platform
  1. Windows
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
:cool:
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hello, this does as you ask
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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).

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top