More Than 3 Conditional Formats

Posted by Sam Brown on May 01, 2001 7:32 AM

Not sure how it is done

I've read one of the earlier posts, but i'm not sure.

I've got data in the range A1:C100. With numbers of anything from 1-200. I want to be able to format the cells colour & font color for each number.

i.e. 1 = red fill & red font, 57 = olive file & olive font, etc etc.

how do i do it??

Sam

Posted by Dave Hawley on May 01, 2001 7:43 AM

Hi Sam

You will need VBA to do this.

Right click on the sheet name tab and select "View Code", paste in this 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

Add as many Case statements as you like. To get the correct color index number just Record a macro applying it to a cell.

Now whenver you enter a number in the range A1:C100 the interior color will change accoringly.

Dave

OzGrid Business Applications

Posted by Dave Hawley on May 01, 2001 7:46 AM

Hi Sam Dim rWatchrange As Range 'Written by OzGrid Business Applications 'www.ozgrd.com 'Allows more than 3 Conditional Formats '''''''''''''''''''''''''''''''''''''''' Set rWatchrange = Range("A1:C100") On Error Resume Next If Intersect(Target, rWatchrange) Is Nothing Then Set rWatchrange = Nothing Exit Sub End If 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 Add as many Case statements as you like. To get the correct color index number just Record a macro applying it to a cell. Now whenver you enter a number in the range A1:C100 the interior color will change accoringly. Dave

For your font use:

Target.Font.ColorIndex = 6

Dave
OzGrid Business Applications