Conditional formating

P

PCOR

Currnetly you can conditional format at cell for up to 3 conditions
Example under 40=red, 41 to 100 = blue and over 101= orange
Is there a way to add a fouth condition? Say under 39 = green
Thanks
 
F

Frank Kabel

Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.

If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell A1:A100 based on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
B

Bob Phillips

The only way to get a fourth is to set the cells to that colour as default,
and any that match the CF conditions will assume one of the other three.
 
P

PCOR

Thanks for the quick response
The first part of your answer works very well to change the font colors.
I am afraid that I do not understand the code you have under
WORKSHEET_change
The first line "If Target.Cells.Count > 1 Then Exit Sub" seems to imply that
is the cell contains a number greater than 1, then exit the sub
If that is the right way of reading that statement, I don't see how the rest
of the code will ever be executed
I sure hope that you can help me with this problem
Thanks
Ian M
 
F

Frank Kabel

Hi
the first VBA statement just checks that only ONE cell is changed (and
not mutiple cells at the same time)
 
M

Myrna Larson

No, it means that if the range that triggered the call to the function
consists of more than one cell, then abort.

The function could be modified to remove that line and in the remaining code
replace Target with Target.Cells(1).
 
P

PCOR

Hi again...I must be very slow today as I can not make that work
Could/would you please show me the required code for there following.
if I enter a value under 20 I want that cell background to turn red and if
it is 20 or over the cell would turn blue.
Many thanks in advance.
Ian M
 
Top