Excel needs more than 3 conditions in condititional formatting...

[Guest]

In Excel conditional formatting will only allow you to add 3 conditions to a
cell. I need to have 4 conditions. Does anyone know a way to work around
this. Has anyone tried visual basic?

 

[JulieD]

Hi

here's a VBA solution i posted earlier

as there's more than 3 conditions you can't use conditional formatting,
however the following code pasted into the "sheet module" of the sheet -
right mouse click on the sheet tab and choose view / code
you should see on the top left of the VBE window your file name in bold (if
not try view / project explorer) and the sheet that you were on selected ...
that's the "sheet module" ... if
the wrong sheet is selected then just double click on the correct one
on the right you should see some white space - copy & paste the code in
there -

assuming you want the conditional formatting to work on cell B6

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell.

if you'ld like additonal help with your criteria & formatting statements,
please feel free to post back with more details.

Cheers
JuileD

 

[Bob Phillips]

Here is a VBA example that sets the cell colour

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "b": .Interior.ColorIndex = 5
Case "o": .Interior.ColorIndex = 46
Case "p": .Interior.ColorIndex = 7
Case "r": .Interior.ColorIndex = 3
'etc.
End Select

End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[Ron Rosenfeld]

In Excel conditional formatting will only allow you to add 3 conditions to a
cell. I need to have 4 conditions. Does anyone know a way to work around
this. Has anyone tried visual basic?

If you search the excel newsgroups, you will find many solutions to this
problem.


--ron

 

[Gord Dibben]

You can have 4 conditions.

One would be the default cell setting.

Three others from CF.

Up to six using John McGimpsey's method.

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

Gord Dibben Excel MVP