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 .

 

[DanteAmor]

thank you once again Dante Amor,

again NO colour, but 'bolds' the entire column????

it must be the laptop I am using as it works fine on my personal laptop.

I will get back to you OK?

You can try a new file, on a new sheet, with new data.
Maybe the sheet has some problems. Try a new book.

 

[Trevor3007]

hi,

I tried it on a completely NEW laptop & still NO colour, but 'bolds' the entire column????

again it works on my personal...wierd eh?

 

[DanteAmor]

hi,

I tried it on a completely NEW laptop & still NO colour, but 'bolds' the entire column????

again it works on my personal...wierd eh?

Pretty weird, maybe the excel version

 

[Trevor3007]

Pretty weird, maybe the excel version

perhaps? its office 2010....

i have this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If LCase(c.Offset(0, 1).Value) = LCase("Matched Assets") Then
                Range("A" & c.Row & ":k" & c.Row).Interior.ColorIndex = 24
            Else
                Range("A" & c.Row & ":k" & c.Row).Interior.ColorIndex = xlNone
            End If
        Next
    End If

which is part of a lot of VB that is on the workbook. Thought, would it be possible to add :-

*locked* = yellow

*call back* = light orange

unsure if the 'wild card would be able to use too?

perhaps you could sort? area applicable would be in the I column BTW

 

[DanteAmor]

perhaps? its office 2010....

i have this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If LCase(c.Offset(0, 1).Value) = LCase("Matched Assets") Then
                Range("A" & c.Row & ":k" & c.Row).Interior.ColorIndex = 24
            Else
                Range("A" & c.Row & ":k" & c.Row).Interior.ColorIndex = xlNone
            End If
        Next
    End If

which is part of a lot of VB that is on the workbook. Thought, would it be possible to add :-

*locked* = yellow

*call back* = light orange

unsure if the 'wild card would be able to use too?

perhaps you could sort? area applicable would be in the I column BTW

I'm lost.


Do not you use this macro anymore?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh As Worksheet, cases As Variant, colors As Variant, i As Long, lr as long
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Set sh = ActiveSheet
        If sh.AutoFilterMode Then sh.AutoFilterMode = False
        cases = Array("Build Completed", "Swapped-Out", "Build Started", "Device Not Received", _
                      "Emailed Requested For SCCM Check", "Desktop UAD - On Hold ATM", _
                      "*Engineer*", "*locked*", "")
        
        colrs = Array(4, 22, 6, 28, 38, 44, 40, 5, xlNone)
        lr = sh.range("H" & rows.count).end(xlup).row
        For i = 0 To UBound(cases)
            sh.Range("A2:H" & lr).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 If
End Sub

 

[Trevor3007]

I'm lost.


Do not you use this macro anymore?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh As Worksheet, cases As Variant, colors As Variant, i As Long, lr as long
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Set sh = ActiveSheet
        If sh.AutoFilterMode Then sh.AutoFilterMode = False
        cases = Array("Build Completed", "Swapped-Out", "Build Started", "Device Not Received", _
                      "Emailed Requested For SCCM Check", "Desktop UAD - On Hold ATM", _
                      "*Engineer*", "*locked*", "")
        
        colrs = Array(4, 22, 6, 28, 38, 44, 40, 5, xlNone)
        lr = sh.range("H" & rows.count).end(xlup).row
        For i = 0 To UBound(cases)
            sh.Range("A2:H" & lr).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 If
End Sub

hello DA,

your code works GREAT on my pers laptop but it does not on my work laptop.

I just had the code that I posted / as shown in my previous.

 

[DanteAmor]

hello DA,

your code works GREAT on my pers laptop but it does not on my work laptop.

I just had the code that I posted / as shown in my previous.

Maybe, you should go back to the version by pgc01 in post #2