Conditional Formatting question

[RonS]

How can I create more than 3 conditional formats. I need 5-6.

 

[Ron Rosenfeld]

How can I create more than 3 conditional formats. I need 5-6.

You cannot.

If you consider that the "base" format is one, then you could have that plus
three conditional formats to come up with four, but that is the limit.

If more are required, then you could would have to write a VBA macro to do the
testing and produce the formatting you wish. These can be written as
event-driven macros and be completely automatic, or you can write them as a
simple macro and select it whenever you wish to do the formatting.

Here is an example of one that I use. I select the cells which I wish to have
formatted and then run the macro. You should get the idea from this.

===========================
Sub PerCentChg()
Dim c As Range

For Each c In Selection
c.NumberFormat = "0.000"

Select Case Application.WorksheetFunction.Round(c.Value, 3)
Case Is >= 1.255
c.Interior.Color = vbWhite
c.Font.Color = vbBlack
Case 1.236 To 1.254
c.Interior.Color = vbCyan
c.Font.Color = vbBlack
Case 1.215 To 1.235
c.Interior.Color = vbMagenta
c.Font.Color = vbWhite
Case 1.201 To 1.214
c.Interior.Color = vbBlue
c.Font.Color = vbWhite
Case 1.18 To 1.2
c.Interior.Color = vbYellow
c.Font.Color = vbBlack
Case 1.166 To 1.179
c.Interior.Color = vbGreen
c.Font.Color = vbBlack
Case 1.155 To 1.165
c.Interior.Color = vbRed
c.Font.Color = vbWhite
Case 1.131 To 1.154
c.Interior.Color = vbBlack
c.Font.Color = vbWhite
Case 1.11 To 1.3
c.Interior.Color = vbBlack
c.Font.Color = vbWhite
Case Else
c.Interior.ColorIndex = xlNone
c.Font.Color = vbBlack

End Select
Next c
End Sub
===================

--ron

 

[J.E. McGimpsey]

Conditional formatting is limited to 3 conditions per cell. To get
the same effect with more conditions, you'll need to use an event
macro.

If you'd like to do that, search the archives - there are thousands
of posts on creating more formats this way:

http://google.com/advanced_group_search?q=group:*excel*