Conditional formatting

[Doug Day]

I need to have cells with 4 formatting conditions. Excel allows up to 3.
Does someone have a way to accomplish this, maybe with a macro?
I know formulas can be used in conditional formatting, as in
=max (A1:a32)=A1
I see in a Excel Macro reference there are Format Macros but am not sure if
I can use them in a conditional Format.
Any suggestions will be appreciated.
Doug

 

[macropod]

Hi Doug,

Here's some vba to get you started.

If you put the code into the relevant worksheet's module, it will apply
coloured backgrounds for various conditions across E5:O25.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row > 4 And .Row < 26 And .Column > 4 And .Column < 16 Then
Select Case .Value
Case 0 To 5.035
.Interior.ColorIndex = 4
Case 5.036 To 10.21
.Interior.ColorIndex = 5
Case 10.211 To 15
.Interior.ColorIndex = 6
Case Is < 0
.Interior.ColorIndex = 7
Case Is >=15.001
.Interior.ColorIndex = 33
Case Else
.Interior.ColorIndex = 0
End Select
End If
End With
End Sub

Modify to suit your needs.

Cheers

 

[JE McGimpsey]

I need to have cells with 4 formatting conditions. Excel allows up to 3.
Does someone have a way to accomplish this, maybe with a macro?
I know formulas can be used in conditional formatting, as in
=max (A1:a32)=A1
I see in a Excel Macro reference there are Format Macros but am not sure if
I can use them in a conditional Format.

If you're formatting font colors, you can get up to 6 without macros:

http://www.mcgimpsey.com/excel/conditional6.html