Conditional Formatting for row based on cell

[candrist]

I have an excel sheet that I am not able to use the conditional formatting built into excel, because I need to do conditional formatting for 5 seperate levels.

The first is the conditional formatting for the term field. if any of the the voluntary or involuntary codes (except NCN) are selected from the drop down I want it to highlight the whole row from column B to column O. according to legend.

Second I want it to highight the row if the comment field says "RSCH IN" or "RSCH OUT".

Any help would be appreciated.

Chris

[matthewspatrick]

Put a sub like this in the sheet module for that worksheet. Edit the conditions and colors as needed.



[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range

Application.EnableEvents = False

If Not Intersect(Me.UsedRange, [m5:m65536]) Is Nothing Then
For Each cel In Intersect(Me.UsedRange, [m5:m65536]).Cells
Select Case cel
Case "Tom", "Dick", "Harry"
Range("b" & cel.Row & ":o" & cel.Row).Interior.Color = vbRed
Case "Mutt", "Jeff"
Range("b" & cel.Row & ":o" & cel.Row).Interior.Color = vbYellow
Case "Jack", "Jill"
Range("b" & cel.Row & ":o" & cel.Row).Interior.Color = vbGreen
Case "Damon", "Pythias"
Range("b" & cel.Row & ":o" & cel.Row).Interior.Color = vbBlue
Case Else
Range("b" & cel.Row & ":o" & cel.Row).Interior.Color = vbBlack
End Select
Next
End If

Application.EnableEvents = True
End Sub
[/VBA]

[Bob Phillips]

With CF

[candrist]

I think that I got it working. thank you patrick and xld.

Chris

[Bob Phillips]

Did you use CF or VBA?

[matthewspatrick]

You're welcome, Chris.

[candrist]

I am actually using all VBA, however there is a small problem with it. Maybe you can assist further.

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cel As Range
Dim cel2 As Range

Application.EnableEvents = False

If Not Intersect(Me.UsedRange, [M5:M34]) Is Nothing Then
For Each cel In Intersect(Me.UsedRange, [M5:M34]).Cells
Select Case cel
Case "NCN"
Range("b" & cel.Row & ":o" & cel.Row).Interior.ColorIndex = 36
Case "ANP", "TYP", "DSH", "OTP", "JOB", "MED", "PAY", "PER", "REL", "RMU", "TMT", "TCI"
Range("b" & cel.Row & ":o" & cel.Row).Interior.ColorIndex = 40
Case "END", "ATT", "DEA", "FDS", "FTR", "INS", "MIS", "RIF", "UNS"
Range("b" & cel.Row & ":o" & cel.Row).Interior.ColorIndex = 45
Case Else
If Not Intersect(Me.UsedRange, [L5:L35]) Is Nothing Then
For Each cel2 In Intersect(Me.UsedRange, [L5:L35]).Cells
Select Case cel2
Case "RSCH IN"
Range("b" & cel2.Row & ":o" & cel2.Row).Interior.ColorIndex = 34
Case "RSCH OUT"
Range("b" & cel2.Row & ":o" & cel2.Row).Interior.ColorIndex = 42
Case Else
'Range("b" & cel2.Row & ":o" & cel2.Row).Interior.ColorIndex = xlNone
End Select
Next
End If
Range("b" & cel.Row & ":o" & cel.Row).Interior.ColorIndex = xlNone
End Select
Next
End If

Application.EnableEvents = True
End Sub[/vba]

The problem is that when I enter RSCH IN or RSCH OUT in the cell L34 it changes to the appropriate color, but then goes to white.

Any Thoughts.

Chris

[Bob Phillips]

You are clearing it after setting it.

But, I think you are using the wrong even t, and doing far too much work.

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "M5:M34"
Const WS_RANGE2 As String = "L5:L34"
Dim cel As Range
Dim cel2 As Range

Application.EnableEvents = False
On Error GoTo ws_exit:

If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
Select Case Target.Value
Case "NCN"
Me.Range("b" & Target.Row & ":o" & Target.Row).Interior.ColorIndex = 36
Case "ANP", "TYP", "DSH", "OTP", "JOB", "MED", "PAY", "PER", "REL", "RMU", "TMT", "TCI"
Me.Range("b" & Target.Row & ":o" & Target.Row).Interior.ColorIndex = 40
Case "END", "ATT", "DEA", "FDS", "FTR", "INS", "MIS", "RIF", "UNS"
Me.Range("b" & Target.Row & ":o" & Target.Row).Interior.ColorIndex = 45
End Select
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
Me.Range("b" & Target.Row & ":o" & Target.Row).Interior.ColorIndex = xlNone
Select Case Target.Value
Case "RSCH IN"
Me.Range("b" & Target.Row & ":o" & Target.Row).Interior.ColorIndex = 34
Case "RSCH OUT"
Range("b" & Target.Row & ":o" & Target.Row).Interior.ColorIndex = 42
Case Else
'Range("b" & Target.Row & ":o" & Target.Row).Interior.ColorIndex = xlNone
End Select
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub
[/vba]