MrExcel Publishing
Your One Stop for Excel Tips & Solutions

applying more than 3 conditional formats to a cell


Posted by Jamie on March 14, 2001 5:42 AM

I have a range of numbers that I need to apply conditional formating to. I need to apply more than the standard 3 colors generated in excel. Is this possible?

ie.
0-10: blue
10-20: green
20-30: yellow
30-40: dark orange
40-50: orange
etc

Posted by Mark W. on March 14, 2001 6:03 AM

As stated in the Excel Help topic, "Apply
conditional formats to cells", "...You can
specify up to three conditions."

Posted by Jamie on March 14, 2001 6:51 AM

But is it possible, through another means such as VB or something else, to get around this limitation?

Posted by Mark W. on March 14, 2001 6:56 AM


> But is it possible, through another means such
as VB or something else, to get around this limitation?

Theoretically, one could mimic conditional formatting
using VBA.

Posted by Dave Hawley on March 14, 2001 7:03 AM


Hi Jamie

This is possible, but you will need some VBA within the Sheet module like below:

'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:A10")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0 To 10
Target.Interior.ColorIndex = 5
Case 10 To 20
Target.Interior.ColorIndex = 10
Case 21 To 30
Target.Interior.ColorIndex = 6
Case 31 To 40
Target.Interior.ColorIndex = 46
Case 41 To 50
Target.Interior.ColorIndex = 45
End Select
End If
End Sub
'END OF CODE


You can add more conditions to this if needed.
It will only apply to the range A1:A10 (Change if needed)
The easiest way to obtain the Color Index numbers is to Record a Macro.

To put this code in:
Right click on the sheet Name tab and select "View Code" then paste the code over the top of what you see.
Push Alt+Q to return to Excel and Save.


Dave

OzGrid Business Applications

Posted by Jamie on March 14, 2001 7:20 AM


Do you know how to do this? How do you mimic conditional formatting in VBA?

Posted by Dave Hawley on March 14, 2001 7:24 AM

Jamie, read my first post!


Dave
OzGrid Business Applications