PDA

View Full Version : Conditional Formatting for row based on cell



candrist
01-05-2007, 07:29 PM
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
01-05-2007, 09:06 PM
Put a sub like this in the sheet module for that worksheet. Edit the conditions and colors as needed.




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

Bob Phillips
01-06-2007, 04:37 AM
With CF

candrist
01-06-2007, 01:24 PM
I think that I got it working. thank you patrick and xld.

Chris

Bob Phillips
01-06-2007, 03:04 PM
Did you use CF or VBA?

matthewspatrick
01-06-2007, 06:32 PM
You're welcome, Chris.

candrist
01-10-2007, 06:02 PM
I am actually using all VBA, however there is a small problem with it. Maybe you can assist further.

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

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
01-11-2007, 02:42 AM
You are clearing it after setting it.

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



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