Conditional formatting

[Peter1973]

Can you put more than 3 conditions in a cell when conditional formatting?

 

[Cbrine]

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

 

[VoG]

This may help http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

 

[Peter1973]

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]

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

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]

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]

Which selected range?

 

[Peter1973]

H7:IA75

 

[Cbrine]

This will limit the range to the specified values.

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