conditional formatting

[Stringhaussen]

Hi,

I'm building a database and need to apply 4 levels of conditional formatting
so each row will change color. The conditions and colors are...

Completed = Green
Awaiting docs = Orange
Credit issue = Red
Declined = Grey

It's range is columns A:Q and the column this data is stored in is "O".

I've tried several variations, but can't get it to work. Any suggestions
would be greatly appreciated.

regards

 

[Rick Rothstein]

Unless you are using XL2007, you can't have 4 different Conditional Formats;
you will need to use this VB event code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If R.Column = 15 Then
Select Case R.Value
Case "Completed"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 35
Case "Awaiting docs"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 45
Case "Credit issue"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 38
Case "Declined"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 15
End Select
End If
Next
End Sub

To implement this event code, right click the name tab for the worksheet
with your data on it, select View Code from the popup menu that appears, and
copy/paste the above code into the code window that appeared. Now go back to
your worksheet. Whenever you enter any of the four phrases you posted, the
row from A to Q will change to the color you asked for (well, a pale version
of the color so you can still see the text easily enough through it). If you
already have data in Column O, then select all of Column O, and then hit
Ctrl+C followed by Ctrl+V (keyboard shortcuts for copy/paste for Column O)
to force the Change event to act upon the existing entries.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and try entering
your values

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Select Case UCase(Target.Value)
Case Is = "COMPLETED"
icolor = 4
Case Is = "AWAITING DOCS"
icolor = 45
Case Is = "CREDIT ISSUE"
icolor = 3
Case Is = "DECLINED"
icolor = 15
Case Else
icolor = xlNone
End Select
Range("A" & Target.Row & ":" & "Q" & Target.Row).Interior.ColorIndex = icolor
End If
End Sub

Mike

 

[Stringhaussen]

Thanks, it worked a treat!!

Regards

Unless you are using XL2007, you can't have 4 different Conditional Formats;
you will need to use this VB event code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If R.Column = 15 Then
Select Case R.Value
Case "Completed"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 35
Case "Awaiting docs"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 45
Case "Credit issue"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 38
Case "Declined"
Range("A" & R.Row & ":Q" & R.Row).Interior.ColorIndex = 15
End Select
End If
Next
End Sub

To implement this event code, right click the name tab for the worksheet
with your data on it, select View Code from the popup menu that appears, and
copy/paste the above code into the code window that appeared. Now go back to
your worksheet. Whenever you enter any of the four phrases you posted, the
row from A to Q will change to the color you asked for (well, a pale version
of the color so you can still see the text easily enough through it). If you
already have data in Column O, then select all of Column O, and then hit
Ctrl+C followed by Ctrl+V (keyboard shortcuts for copy/paste for Column O)
to force the Change event to act upon the existing entries.

 

[Stringhaussen]

Thanks for you help,

Regards

Hi,

Right click your sheet tab, view code and paste this in and try entering
your values

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Select Case UCase(Target.Value)
Case Is = "COMPLETED"
icolor = 4
Case Is = "AWAITING DOCS"
icolor = 45
Case Is = "CREDIT ISSUE"
icolor = 3
Case Is = "DECLINED"
icolor = 15
Case Else
icolor = xlNone
End Select
Range("A" & Target.Row & ":" & "Q" & Target.Row).Interior.ColorIndex = icolor
End If
End Sub

Mike