How do I set more then 3 Condition for a cell?

[Charlie Lam]

I have cells that I want to change color automatically when I input a certain
value, Letter or Number. However the conditional format only allows me to do
3 conditions/colors. Is there a way I can add more then 3 conditions?

 

[Otto Moehrbach]

You can use VBA (programming) and then there is no limit. If you want to go
this route, post back with details about what cells, what conditions, what
colors. HTH Otto

 

[Liliana]

I have cells that I want to change color automatically when I input a
certain value, Letter or Number. However the conditional format only
allows me to do 3 conditions/colors. Is there a way I can add more
then 3 conditions?

Using Excel 2003 I presume. Excel 2003 limits you to three conditional
formats but you can also work with font colours. See:
http://www.ozgrid.com/Excel/font-formats.htm

If this doesn't extend your range sufficiently, the VBA solution:

http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

 

[Gord Dibben]

Copy/paste this code to a sheet module.

Edit to suit.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub


Gord Dibben MS Excel MVP