Multi Conditional Formats - Still Stuck (Dave Hawley)

Posted by Sam Brown on May 25, 2001 6:13 AM

I had this bit of code(see below) from Dave Hawley (thanks) to allow multi conditonal formats, but i'm a little confused on how to code what i need.

The code selects a1:c100 (which contains numbers 1-200), I want to be able to color the cells containing "25", "37", "98", "175" & "196". So all instances of 25 will be red, all 37's will be blue, etc etc.

I'm confused on how the below code should be coded?? Any help would be great.

&LT;code>

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rWatchrange As Range
'Written by OzGrid Business Applications
'www.ozgrd.com

'''''''''''''''''''''''''''''''''''''''
'Allows more than 3 Conditional Formats
''''''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Then Exit Sub
Set rWatchrange = Range("A1:C100")
On Error Resume Next
If Intersect(Target, rWatchrange) Is Nothing Then
Set rWatchrange = Nothing
Exit Sub
End If

Select Case Target
Case 1 To 5
Target.Interior.ColorIndex = 6
Case 6 To 10
Target.Interior.ColorIndex = 46
Case 11 To 15
Target.Interior.ColorIndex = 5
End Select

End Sub

Posted by Kevin James on May 25, 2001 12:13 PM

Sam:

I just copied and pasted the code and it works as written.

To help you understand Dave's code, the CASE statements set up a range of numbers. Each statement colorizes based on the value in the range.

For example:
Case 1 To 5
Target.Interior.ColorIndex = 6

If the value you type is in the range of 1 to 5, the cell will be yellow (ColorIndex value 6).

If you are looking to color only the values you posted in this latest message, then you can change the case statement to only one value per highlight color. Seeing as you listed more numbers that Dave provided CASEs, you will need to expand the code to include those numbers and then color them with different ColorIndex numbers. I don't have a list of what those are, but play around with them and see what colors you can generate with different numbers. You can't hurt it.

Kevin

Posted by Kevin James on May 25, 2001 5:09 PM

more info

Hi Sam,

With the help of Barrie Davidson on a function I was trying to create, I figured out one way you can determine the values of interior color.

Put this function in a module
Function CN(CNbr)
CN = CNbr.Interior.ColorIndex
End Function

Then, on your spreadsheet, choose a background for a cell. For example, highlight cell A1 with yellow. Now in B1, put the function call (formula) =CN(A1)

Take care,
Kevin

Posted by Dave Hawley on May 25, 2001 5:10 PM

Hi Sam and Kevin

Sam, as Kevin has said my code will change the fill color of a cell based on it's content. But it will not effect entries made prior to implementing the code. The code MUST be placed within the Sheet module of the Worksheet. To do this right click on the Sheet name tab and select "View Code" and paste in the code, push Alt+Q to return to Excel. Now anytime you type number within the "WatchRange" it will change accordingly.

The code is only really to give the user a starting point and to show that it is possible to have more than 3 conditional formats via VBA. The code would need to be added to and/or modified to suit eg; add more "Case" statements. If you need specific help please ask.

Best way to get the color Index you need is to type "Color Index" in the help within the VBE. Another method is to Record a macro.

Dave

OzGrid Business Applications