castak
03-06-2019, 01:06 AM
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 ?
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 ?