Conditional Formatting a whole line based on cell value

  • Thread starter Thread starter Maver1ck666
  • Start date Start date
M

Maver1ck666

Okay, I have a spreadsheet with over 1000 lines of data in all at different
status' of progress.

What I would like to do it to highlight the whole line a certain colour
depending on the status value.

I currently have the max 3 conditional formats doing this but I need to
expand them and there are over 10 different status'.

My spreadsheet runs from A to L (with the status being I). Is there any way
the formatting could loop to check the whole spreadsheet?

Thanks in advanced for your help.

Maver1ck666
 
Best to do that with event code, trigger the code when the target column
changes

As an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H:H" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Entirerow.Interior.ColorIndex = 3 'red
Case 2: .Entirerow.Interior.ColorIndex = 6 'yellow
Case 3: .Entirerow.Interior.ColorIndex = 5 'blue
Case 4: .Entirerow.Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top