Shading Rows With IF Statements?

[JPS]

I have a large spreadsheet and based on a status indicator in Column DW, I
need to shade the entire row one of eight colors, For example, if a cell
value in row DW is L the whole row needs to be shaded gray. Another example.
is if DW is F, the row needs to be Blue. If DW is blank, the row is not
shaded. Is this doable with IF statements; I already use Conditional
Formatting for another purpose. I am using Excel 2003

 

[Bob Phillips]

You need VBA.


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "DWW" '<=== 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 "L": .EntireRow.Interior.ColorIndex = 48 '40% grey
Case "F": .EntireRow.Interior.ColorIndex = 5 'blue
'etc
Case Else: .EntireRow.Interior.ColorIndex = xlColorIndexNone
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.

 

[JPS]

Bob,
Sorry for taking so long to respond I have been working on a rush project.
Two considerations... Since I cannot spell VBA and the spreadsheet is
maintained by my clerical staff, I am looking for a simpler solution. I have
looked at the conditional formatting that I am currently using and I can
handle another way. If I use conditional formatting can I get 6 colors, I
would like 8 but I can manager with 6, and how do I shade the entire row.

Thanks for your help.

 

[Bob Phillips]

select the whole row and use a condition locked on one cell, such as

=$C2>10

 

[JPS]

Bob,
Can I expand conditional formatting to get 6 colors?

 

[Gord Dibben]

In 2003 you are limited to 3 conditions..........4 if you count default.

You are going to have to learn to spell VBA


Gord Dibben MS Excel MVP