change colour based on wildcard?

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
667
Office Version
  1. 365
Platform
  1. Windows
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.








Code:
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,
Code:
 [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 .
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi Trevor

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

Code:
 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.
 
Upvote 0
You can also try something like this.


Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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 ...
 
Upvote 0
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
Code:
    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
Code:
    cases = Array("Build Completed", "Swapped-Out", "Build Started", "Device Not Received", _
                  "Emailed Requested For SCCM Check", "Desktop UAD - On Hold ATM", _
                  "*Engineer*", "*locked*")
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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