VBA if the first 6 characters in a Cell DO NOT equal 01/01/ then

Viewed 2537

I hope you can help. I have a piece of code below and it is does not seem to be working correctly

What I want the code to do is this

if the first 6 characters of any cell in the entire length of Column L does not contain 01/01/ then highlight that cell with interior color 27

At the moment as you can see in Screen Shot 1 every cell in Column L is highlighted with interior color 27 , only Cell L2 and L5 should be colored.

My Code is below can it be amended to only highlight the cells that do not contain 01/01/ as the first 6 Characters in Column L

> PS the Conditional formatting approach is not an option

As always any and all help is greatly appreciated.

Screen Shot 1 enter image description here

MY CODE

Sub Colour_If()
    RowCount = Cells(Cells.Rows.Count, "L").End(xlUp).Row
    For Each n In Range("L2:L" & RowCount)
        n = Left(n, 6)
        If n <> "01/01/" Then
        Range("L2:L" & RowCount).Interior.ColorIndex = 24

        End If
    Next n
End Sub
3 Answers
Related