multiple conditional formats

[RC]

How can I set up a worksheet for more than 3 conditional formats

 

[Myrna Larson]

You can't. You would have to use a VBA macro to apply the formatting, which
would be static and not change with changes in the cell values -- you'd have
to run the macro again.

 

[Ron Rosenfeld]

How can I set up a worksheet for more than 3 conditional formats

You can use an event-triggered VBA routine.

To enter this, right click on the sheet tab and select View Code. Then paste
the code below into the window that opens.

Edit the area of interest, values, characteristics of the cell you wish to
change,etc. Look at help for color, colorindex, interior, font,etc for some
guidance on how to make appropriate changes.

For example, to assign different colors depending on value:

===================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range

Set aoi = [a1:a100] 'set this range to area to _
be conditionally formatted

For Each c In aoi
Select Case c.Value
Case Is < 1
c.Interior.ColorIndex = xlNone
Case 1 To 3
c.Interior.Color = vbGreen
Case 3 To 5
c.Interior.Color = vbRed
Case 5 To 10
c.Interior.Color = vbBlue
Case Else
c.Interior.ColorIndex = xlNone
End Select
Next c
=========================

--ron

 

[HWade]

I have an additional question regarding more than 3 condtions. After I apply
different colors to the cells, how do I ask the cells to turn BOLD. Where do
I insert the command??

How can I set up a worksheet for more than 3 conditional formats

You can use an event-triggered VBA routine.

To enter this, right click on the sheet tab and select View Code. Then paste
the code below into the window that opens.

Edit the area of interest, values, characteristics of the cell you wish to
change,etc. Look at help for color, colorindex, interior, font,etc for some
guidance on how to make appropriate changes.

For example, to assign different colors depending on value:

===================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range

Set aoi = [a1:a100] 'set this range to area to _
be conditionally formatted

For Each c In aoi
Select Case c.Value
Case Is < 1
c.Interior.ColorIndex = xlNone
Case 1 To 3
c.Interior.Color = vbGreen
Case 3 To 5
c.Interior.Color = vbRed
Case 5 To 10
c.Interior.Color = vbBlue
Case Else
c.Interior.ColorIndex = xlNone
End Select
Next c
=========================

--ron

 

[Ron Rosenfeld]

I have an additional question regarding more than 3 condtions. After I apply
different colors to the cells, how do I ask the cells to turn BOLD. Where do
I insert the command??

You do that in the same segment where you set the color.

For example:

Case 1 To 3
c.Interior.Color = vbGreen
c.Font.Bold = True

Don't forget, though, that in the instances where you don't want it to be Bold,
that you should explicitly set it to False. In my example, that would be for
Case < 1 and Case Else.


--ron