Conditional formatting

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
957
Can you put more than 3 conditions in a cell when conditional formatting?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Conditional Formating only supports 3 conditions. If you want more, then you are looking at VBA event Driven programming to accomplish this. If you are interested, post back with your conditions, and I can whip something up for you.

Cal
 
Upvote 0
I want to format a range of cells so that they are couloured differently depending on what is in them , i have the following list of data :

W=Blue
R=Red
H=Green
S=Grey
OA=Purple
SH=Brown
and if it is a time ie 12:30 = Yellow

Thanks
 
Upvote 0
Peter,
Just add this code to the worksheet level of the sheet you wish to have the conditional formating act upon.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

Select Case Target
    Case "W"
        Target.Interior.ColorIndex = 41
    Case "R"
        Target.Interior.ColorIndex = 3
    Case "H"
        Target.Interior.ColorIndex = 4
    Case "S"
        Target.Interior.ColorIndex = 15
    Case "OA"
        Target.Interior.ColorIndex = 13
        Target.Font.ColorIndex = 36
    Case "SH"
        Target.Interior.ColorIndex = 53
        Target.Font.ColorIndex = 36
    Case ""
        Target.Interior.ColorIndex = False
        Target.Font.ColorIndex = False
    Case TimeValue(Target.Text)
        If Err.Number <> 13 Then
                Target.Interior.ColorIndex = 36
        End If
    Case Else
        Target.Interior.ColorIndex = False
        Target.Font.ColorIndex = False
End Select

End Sub

HTH
Cal
 
Upvote 0
Sorry to sound thick but how do I put it in the sheet and can I do it for only a selected ammount of cells in the worksheet?
 
Upvote 0
This will limit the range to the specified values.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not (Intersect(Range("H7:IA75"), Target) Is Nothing) Then
        Select Case Target
        Case "W"
            Target.Interior.ColorIndex = 41
        Case "R"
            Target.Interior.ColorIndex = 3
        Case "H"
            Target.Interior.ColorIndex = 4
        Case "S"
            Target.Interior.ColorIndex = 15
        Case "OA"
            Target.Interior.ColorIndex = 13
            Target.Font.ColorIndex = 36
        Case "SH"
            Target.Interior.ColorIndex = 53
            Target.Font.ColorIndex = 36
        Case ""
            Target.Interior.ColorIndex = False
            Target.Font.ColorIndex = False
        Case TimeValue(Target.Text)
            If Err.Number <> 13 Then
                    Target.Interior.ColorIndex = 36
            End If
        Case Else
            Target.Interior.ColorIndex = False
            Target.Font.ColorIndex = False
        End Select
End If
End Sub

HTH
Cal
 
Upvote 0

Forum statistics

Threads
1,217,753
Messages
6,138,416
Members
450,135
Latest member
IsidroGil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top