more than 3 conditional formating

Posted by phil on December 18, 2001 4:40 AM

does any1 know how i can specify more than 3 conditional formaing rules....

i have a spreadsheet that uses eight numbers and i would like each of them to appear in a different colour.....

thanxs

phil

Posted by Juan Pablo G. on December 18, 2001 5:46 AM

You need VBA for this, how are you trying to accomplish ? Care to post some data examples and your wish-to-do example ?

Juan Pablo G.

Posted by sandra on December 18, 2001 6:08 AM

Hi Phil

In the Visual Basic Editor (Alt+F11) or
Tools, Macro, Visual Basic Editor Insert a new module
Write down the following macro

Sub ChangeCellColor()

For Each c In ActiveSheet.Range("A1:D10")

Select Case Val(c.Value)
Case 0.01
c.Interior.ColorIndex = 30
c.Font.ColorIndex = 1
Case 4
c.Interior.ColorIndex = 31
c.Font.ColorIndex = 2
Case 6
c.Interior.ColorIndex = 32
c.Font.ColorIndex = 3
Case 8
c.Interior.ColorIndex = 33
c.Font.ColorIndex = 4
Case 10
c.Interior.ColorIndex = 34
c.Font.ColorIndex = 5
Case 12
c.Interior.ColorIndex = 35
c.Font.ColorIndex = 6
Case 14
c.Interior.ColorIndex = 36
c.Font.ColorIndex = 7
Case 16
c.Interior.ColorIndex = 37
c.Font.ColorIndex = 8
Case Else
c.Interior.ColorIndex = xlNone
c.Font.ColorIndex = xlAutomatic
End Select

Next c

End Sub

To Run this macro : (Alt+F8) or
Tools, Macro, Macro
select ChangeCellsColor and Click Run

You can change:
- range for search
- color index as you want

Hope it'll help

Sandra