Conditional formatting

Peter1973

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
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
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

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
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
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?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Which selected range?
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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
Top