VBA, conditional formatting case icolor wildcards

[poolio]

Can't seem to find an answer to what I think is a relatively simple query....

I'm currently using this code to 'conditionally format' a range of cells containing various text strings...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
Dim colorr As Integer

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("M2:IV1243")) Is Nothing Then
Select Case Target
               
Case ""
icolor = 46
Case "On Site"
icolor = 38
Case "On Site - am"
icolor = 38
Case "On Site - pm"
icolor = 38
Case "On Site(?)"
icolor = 40
Case "B.Hol"
icolor = 5
colorr = 6
Case "Int. Meeting"
icolor = 43
Case "Ext. Meeting"
icolor = 45
Case "Leave"
icolor = 8
Case "Leave(?)"
icolor = 34
Case "MP Training"
icolor = 39
Case "Medical/Dental"
icolor = 50
Case "Ext. Training"
icolor = 44
Case "Sick"
icolor = 12
colorr = 6
Case "Leave (1/2)"
icolor = 8

End Select
Target.Interior.ColorIndex = icolor
Target.Font.ColorIndex = colorr

End If
End Sub

Just wondering if there's a way to use a wildcard so if someone types "Leave - Spain" it still uses icolor 8...
I was thinking this might have worked - the old * wildcard trick but alas no:

 Case "Leave*"
icolor 8

Or even using a LEFT function

 Case Left("Leave", 5)
icolor 8

All help warmly received.

Ta
Poo

 

[Jerry Sullivan]

Hi Poo,

You could use the Like Operator for pattern matching and "=" for exact matches....

Option Compare Text '--change Text to Binary for Case-Sensitive matching

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim icolor As Integer
    Dim colorr As Integer
    Dim sTask As String
    
    If Target.Cells.Count > 1 Or _
        Intersect(Target, Range("M2:IV1243")) Is Nothing Then Exit Sub
    sTask = Target.Text
    Select Case True
        Case sTask = "": icolor = 46
        Case sTask = "On Site(?)": icolor = 40
        Case sTask Like "On Site - ?m": icolor = 38
        Case sTask = "B.Hol"
            icolor = 5
            colorr = 6
        Case sTask = "Int. Meeting": icolor = 43
        Case sTask = "Ext. Meeting": icolor = 45
        Case sTask = "Leave(?)": icolor = 34
        Case sTask Like "Leave*": icolor = 8
        Case sTask = "MP Training": icolor = 39
        Case sTask = "Medical/Dental": icolor = 50
        Case sTask = "Ext. Training": icolor = 44
        Case sTask = "Sick"
            icolor = 12
            colorr = 6
        Case Else
            '--what color if none of above?
    End Select
    With Target
        .Interior.ColorIndex = icolor
        .Font.ColorIndex = colorr
    End With
End Sub

 

[poolio]

JS,

Awesome solution....

Although modified this bit:

        Case sTask Like "On Site - ?m": icolor = 38

to this

        Case sTask Like "On Site*": icolor = 38

Bit of tweaking and I've allowed narratives for everything - so useful - gold star...

Poo

 

[Jerry Sullivan]

You're most welcome!