more than 3 conditional formating in excel

[Guest]

Hi
I am new to conditional formating in Excel.

In row 2 I need to enter nos. between 1-5. I want each color to have a
particular color. I have managed to do 4 (3 with conditional formating and
the 4th retaining the default color).

Is there a way i can do all 5 colors?

Thanks

 

[Ken Wright]

You need to use VBA. Try this:-

Private Sub Worksheet_Calculate()
'Code must be placed in the codemodule of the actual sheet you are working
with.
Dim oCell As Range
For Each oCell In Range("A1:A20")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone
Case Is = 1
oCell.Interior.ColorIndex = 5
Case Is = 2
oCell.Interior.ColorIndex = 3
Case Is = 3
oCell.Interior.ColorIndex = 6
Case Is = 4
oCell.Interior.ColorIndex = 4
Case Is = 5
oCell.Interior.ColorIndex = 7
Case Is = 6
oCell.Interior.ColorIndex = 15
Case Is = 7
oCell.Interior.ColorIndex = 40
Case Is > 7
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

 

[Gord Dibben]

Maman

How about 6 colors without using code?

JE McGimpsey points out a method of combining CF and Custom format.

http://www.mcgimpsey.com/excel/conditional6.html


Gord Dibben Excel MVP