I am not very good in VBA but I already looked for an answer online for hours. Hope you can help me.

I would like to insert rows above cells that are filled with a specific color (two different color indexes). Is this actually possible? I only have text in column A. So I would need a kind of formula, like "if one cell in column A is black or grey (colorindex 1 and ?), insert one blank, white row above.

Thank you very much for your answers.

closed as too broad by DavidPostill, Ƭᴇcʜιᴇ007, mdpc, fixer1234, Excellll Mar 10 '15 at 15:01

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


With data like:

enter image description here

Running this macro:

Sub RowYourBoat()
    Dim r1 As Range, r2 As Range, r3 As Range
    Set r1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    Set r3 = Nothing

    For Each r2 In r1
        If r2.Interior.ColorIndex = 1 Then
            If r3 Is Nothing Then
                Set r3 = r2
                Set r3 = Union(r3, r2)
            End If
        End If
    Next r2

    If r3 Is Nothing Then
    End If
End Sub

will produce:

enter image description here

This is for black, the ColorIndex for grey depends on what shade of grey you use.

Not the answer you're looking for? Browse other questions tagged or ask your own question.