Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Re: applying more than 3 conditional formats to a cell

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."

Re: applying more than 3 conditional formats to a cell

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?


Re: applying more than 3 conditional formats to a cell

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.


Re: applying more than 3 conditional formats to a cell

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


Re: applying more than 3 conditional formats to a cell

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?


Re: applying more than 3 conditional formats to a cell

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

Jamie, read my first post!


Dave
OzGrid Business Applications


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.