VBA code help when there is a new value in column insert new blank line

[LNG2013]

I need help with some VBA code that would look at my data and when there is a new value in column B or C insert a new blank line above the new value. I also need it to ignore other blank lines and only look at new values.

Bonus points if the word NEW can be inserted in the newly created row.


So like this:

 

[Dannyh1]

Hi,

Try:

Sub Test()
Dim rng As Range
Dim cl
Set rng = Range("C1:C100")
For Each cl In rng
    If cl.Value <> "" Then
 
        If cl.End(xlDown).Value <> cl.Value Or cl.Offset(0, 1).End(xlDown).Value <> cl.Offset(0, 1).Value Then
 
            cl.End(xlDown).EntireRow.Insert
 
        End If
 
    End If
 
Next cl
For Each cl In rng
    If cl.Value <> "" Then
 
        If cl.End(xlDown).Value <> cl.Value Or cl.Offset(0, 1).End(xlDown).Value <> cl.Offset(0, 1).Value Then
 
            Range(cl.End(xlDown).Offset(-1, 0), cl.End(xlDown).Offset(-1, 1)).Interior.ColorIndex = 6
            cl.End(xlDown).Offset(-1, 0).Value = "New"
 
 
        End If
 
    End If
 
Next cl
End Sub

 

[Dannyh1]

you're welcome