Worksheet_Change problem

[liampog]

Hi there

I'm trying to enter the following code into my Worksheet_Change SUB but it doesn't work properly:

If Target.Offset(0, -1).Text = "*[Insp]*" Then
    Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 37)).Interior.ColorIndex = 24
    Else: Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 37)).Interior.Color = xlNone
End If

Basically, when the cell to the direct left of the Target cell contains [Insp] anywhere in the cell I want it to colour the cells to the right of the Target cell... but I don't know how to include wildcards in this.

I tried with the asterisks (*) but it's not working.

Any suggestions? I'm sure it can be done, but I don't know how.

Thanks in advance for the help.

Liam

 

[Jaafar Tribak]

Maybe this :

If InStr(1, Target.Offset(0, -1).Text, "[Insp]", vbTextCompare) Then

 

[liampog]

Works perfectly!

Thanks so much for the speedy reply!

Liam

 

[antaeusguy]

Hi there

I'm trying to enter the following code into my Worksheet_Change SUB but it doesn't work properly:

If Target.Offset(0, -1).Text = "*[Insp]*" Then
    Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 37)).Interior.ColorIndex = 24
    Else: Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 37)).Interior.Color = xlNone
End If

Basically, when the cell to the direct left of the Target cell contains [Insp] anywhere in the cell I want it to colour the cells to the right of the Target cell... but I don't know how to include wildcards in this.

I tried with the asterisks (*) but it's not working.

Any suggestions? I'm sure it can be done, but I don't know how.

Thanks in advance for the help.

Liam

I tried this and it might work

when the cell to the direct left of the Target cell contains [Insp] anywhere in the cell, I want it to colour the cells to the right of the Target cell

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Offset(, -1).Value Like "*[[]Insp]*" Then
        Target.Offset(, 1).Resize(, 37).Interior.ColorIndex = 24
    End If
End Sub