Problem with like

[castak]

Hello everyone,

I've got a macro to highlight cells with Like.
Let me explain :
In my Range("A2:A14"), I've got "AB" in A2, then "B" in A3 and "A" from A4 to A14.


Sub Test()

Dim Var As String
Dim rng As Range, cel As Range


Set rng = Sheets(1).Range("A2:A14")


Var = "A"
For Each cel In rng
If InStr(1, Var, cel) = 0 Then
cel.Interior.Color = vbBlue
Else
cel.Interior.ColorIndex = xlNone
End If
Next cel




End Sub

In my VAR I put any letter and for each cel in my range, if a cell does'nt match with my VAR, this cell highlights in blue.
Actually, if I put "AB" in my Var, my whole range is xlnone (it works). If I put "A" in my Var, the range with "B" as value only should be in blue but there's a problem here because "AB" is in blue as well whereas it should be xlnone. There is the same problem when I put "B" in my Var, all the cells with the value "A" are in blue (ok) but "AB" is also in blue whereas it should xlnone.
Have someone got an idea to sort out this ?

[大灰狼1976]

Hi castak!
change
If InStr(1, Var, cel) = 0 Then
into
If InStr(1, cel, Var) = 0 Then
and try it again.

[castak]

Hi!
I already did it before but by doing so, the case "A" and "B" in my Var work but the case "AB" doesn't work anymore because the cel with "B" is in blue just like all the cells with "A" whereas they should be xlnone ...

[大灰狼1976]

I just thought it wrong.

Sub Test()
Dim Var As String, i&, b As Boolean
Dim rng As Range, cel As Range
Set rng = Sheets(1).Range("A2:A14")
Var = "B"
For Each cel In rng
b = False
For i = 1 To Len(Var)
If InStr(1, cel, Mid(Var, i, 1)) Then
cel.Interior.ColorIndex = xlNone
b = True
Exit For
End If
Next i
If b = False Then cel.Interior.Color = vbBlue
Next cel
End Sub

[castak]

It works perfectly thanks again and again and again vba guy! :thumb

[大灰狼1976]

You're welcome:friends::beerchug::beerchug: