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

  • Thread starter Thread starter Charlie Lam
  • Start date Start date
C

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?
 
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
 
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?
Click to expand...

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
 
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
 
Back
Top