excel vba to insert black row in filtered data

[dshafique]

Hey guys, I'm trying to create a vba which inserts a blank color filled row above every cell in Column B which contains the text "BASE".

pseudo code:
if cell contains "BASE"
then insert blank row above and fill the whole row in Black
else move on to next cell

 

[Joe4]

Try this:

Sub InsertBlackRows()

    Dim myRow As Long
    Dim lastRow As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column B
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row

'   Loop through all rows in column B backwards
    For myRow = lastRow To 1 Step -1
        If UCase(Cells(myRow, "B")) = "BASE" Then
            Rows(myRow).Insert
            With Rows(myRow).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorLight1
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next myRow
        
    Application.ScreenUpdating = True
    
End Sub

 

[dshafique]

Thanks! worked perfectly!

 

[Joe4]

You are welcome!