change colour based on wildcard?

[Trevor3007]

evening,

i have the code below (it runs within more code, I just extracted & added the 'end sub')

Works great in the workbook that it was originally designed for.

Private Sub Worksheet_Change(ByVal Target As Range)
 
Application.ScreenUpdating = False
    Dim rng As Range
      
    For Each rng In Range("h3:h8000")
        Select Case rng.Value
            Case "Build Completed"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 4
                    .Font.Bold = True
                End With
            Case "Swapped-Out"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 22
                    .Font.Bold = True
                End With
            Case "Build Started"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 6
                    .Font.Bold = True
                End With
            Case "Device Not Received"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 28
                    .Font.Bold = True
                End With
            Case "Emailed Requested For SCCM Check"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 38
                    .Font.Bold = True
                End With
            Case "Desktop UAD - On Hold ATM"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 44
                    .Font.Bold = True
                End With
            Case "Device With Build Engineer"
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = 40
                    .Font.Bold = False
                End With
            Case ""
                With Range("A" & rng.Row).Resize(1, 22)
                    .Interior.ColorIndex = xlNone
                    .Font.Bold = False
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
    End sub

however, I would wish to use it in another workbook,

 [COLOR=#ff0000]For Each rng In Range("h3:h8000")
        Select Case rng.Value
            Case "3rd floor engineer door - locked"[/COLOR]

The red text above , is it possible to use a wildcard IE *locked' and it will trigger the colour change?
I tried , but no luck. Hoping someone has the knowledge?


Fingers crossed .

 

[pgc01]

Hi Trevor

To do it test rng.Value in each case branch, like

 For Each rng In Range("h3:h8000")
        Select Case True
            Case rng.Value Like "*locked*"
                ' some code
            Case rng.Value Like "*ATM*"
                ' some code
etc.

 

[DanteAmor]

You can also try something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    '''''
    Dim sh As Worksheet, cases As Variant, colors As Variant, i As Long
    Application.ScreenUpdating = False
    Set sh = ActiveSheet
    cases = Array("Build Completed", "Swapped-Out", "Build Started", "Device Not Received", _
                  "Emailed Requested For SCCM Check", "Desktop UAD - On Hold ATM", _
                  "[COLOR=#0000ff]*Engineer*[/COLOR]", "[COLOR=#0000ff]*locked*[/COLOR]", "")
    
    colrs = Array(4, 22, 6, 28, 38, 44, 40, 5, xlNone)
    For i = 0 To UBound(cases)
        sh.Range("A2:H8000").AutoFilter 8, cases(i)
        sh.AutoFilter.Range.Offset(1).Interior.ColorIndex = colrs(i)
        sh.AutoFilter.Range.Offset(1).Font.Bold = True
    Next
    sh.ShowAllData
    '''''
    
End Sub

 

[pgc01]

Much better Dante.
I just read the last part of the post and answered to the syntax question.
Your solution answers the OP's problem in a much more efficient way.

 

[DanteAmor]

Much better Dante.
I just read the last part of the post and answered to the syntax question.
Your solution answers the OP's problem in a much more efficient way.

Thanks PGC, we hope that some solution will help the OP

 

[Trevor3007]

hiya Dante Amor,


thank you for your code. Works , but if i remove the text , the colour stays? Are you able to sort so if the text is removed from the applicable cell(s) it will 'no fill' the applicable row?

 

[Trevor3007]

hiya PGC

very much apprecieted your help. But used DanteA's as you agreed it was the better way.
Works apart when i remove the applicable text from the cell, the colour remains? I have asked DanteAmor if he can sort ...

 

[DanteAmor]

hiya Dante Amor,


thank you for your code. Works , but if i remove the text , the colour stays? Are you able to sort so if the text is removed from the applicable cell(s) it will 'no fill' the applicable row?

I'm not sure what you need now, but try the following.

Change this line

    cases = Array("Build Completed", "Swapped-Out", "Build Started", "Device Not Received", _
                  "Emailed Requested For SCCM Check", "Desktop UAD - On Hold ATM", _
                  "*Engineer*", "*locked*"[COLOR=#ff0000], ""[/COLOR])

by

    cases = Array("Build Completed", "Swapped-Out", "Build Started", "Device Not Received", _
                  "Emailed Requested For SCCM Check", "Desktop UAD - On Hold ATM", _
                  "*Engineer*", "*locked*")

 

[Trevor3007]

Hello DanteA,

sorry for the confusion. will try to make it give a clearer explaination for you.

So a cell h24 contains 'locked' and the row changes to the applicable colour. If I remove 'locked' from cell h24, the 'colour' remains, it should 'no fill' the row.

hope this helps & you can solve?

 

[DanteAmor]

Hello DanteA,

sorry for the confusion. will try to make it give a clearer explaination for you.

So a cell h24 contains 'locked' and the row changes to the applicable colour. If I remove 'locked' from cell h24, the 'colour' remains, it should 'no fill' the row.

hope this helps & you can solve?

what do you mean by 'no fill' the row.
The first macro, if you delete a data from the cell, puts the cell without color.
Do you have more code or conditional format that is painting the cell?