Conditional formating
 

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

--
Norton Professional 2004 says this email is clean...believe it

Conditional formating
 

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



--
Regards
Frank Kabel
Frankfurt, Germany


PCOR wrote:
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

Conditional formating
 

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.

--
HTH

-------

Bob Phillips
"PCOR" wrote in message
gers.com...
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

--
Norton Professional 2004 says this email is clean...believe it

Conditional formating
 

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


"Frank Kabel" wrote in message
...
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



--
Regards
Frank Kabel
Frankfurt, Germany


PCOR wrote:
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

Conditional formating
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


PCOR wrote:
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


"Frank Kabel" wrote in message
...
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



--
Regards
Frank Kabel
Frankfurt, Germany


PCOR wrote:
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

Conditional formating
 

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).


On Wed, 28 Jul 2004 21:38:08 GMT, "PCOR" wrote:

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


"Frank Kabel" wrote in message
...
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



--
Regards
Frank Kabel
Frankfurt, Germany


PCOR wrote:
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

Conditional formating
 

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
"Frank Kabel" wrote in message
...
Hi
the first VBA statement just checks that only ONE cell is changed (and
not mutiple cells at the same time)

--
Regards
Frank Kabel
Frankfurt, Germany


PCOR wrote:
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


"Frank Kabel" wrote in message
...
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



--
Regards
Frank Kabel
Frankfurt, Germany


PCOR wrote:
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