How to Insert Blank Row with Shaded Background When Value in Column A Changes, While Ignoring Other Blank Rows

[kokoanutt]

I already have a Macro to insert blank rows when the value in columns C and G change, but now I want to insert a blank row with a shaded background when the value in Column A changes while ignoring other blank rows. Please help!

Sub BLANKS_SUMMARY2()

    Dim ws As Worksheet
    Dim lr As Long
    Dim i As Long
   
    Set ws = Worksheets("Logged Units Report")
    lr = ws.Range("C" & Rows.Count).End(xlUp).Row
    For i = lr - 1 To 2 Step -1
        If ws.Range("C" & i).Value <> ws.Range("C" & i + 1).Value _
            Or ws.Range("G" & i).Value <> ws.Range("G" & i + 1).Value Then
                ws.Range("C" & i + 1).EntireRow.Insert
        End If
    Next i

End Sub

 

[iliace]

To clarify: you already ran the macro that inserted blank rows based on C and G, and you want to run another macro to insert blank rows based on row A?

You can do something like this (replace 65535 with whatever color you want):

Sub BLANKS_SUMMARY3()

    Dim ws As Worksheet
    Dim lr As Long
    Dim i As Long
 
    Set ws = Worksheets("Logged Units Report")
    lr = ws.Range("C" & Rows.Count).End(xlUp).Row
    For i = lr - 1 To 2 Step -1
      If Len(ws.Range("A" & i) > 0 And Len(wsh.Range("A" & i + 1) > 0 _
            And ws.Range("A" & i).Value <> ws.Range("A" & i + 1).Value Then
         ws.Range("A" & i + 1).EntireRow.Insert
         ws.Range("A" & i + 1).EntireRow.Interior.Color = 65535
      End If
    Next i

End Sub

If you are doing this regularly or repeatedly (for C G and now also A), I would incorporate this into the original code.