How to use VBA code to clear cells based on specific criteria

< Back to Search results

 Category: [Excel]  Demo Available 

How to use VBA code to clear cells based on specific criteria




The user has a worksheet with columns A to F. Column F is my reference column. The user wrote a subroutine that looks for the word "Header" in Column F, and then colors the entire row(s) gray. Now, the focus is on Column B. In Column B, all cells that are effectively not shaded/colored grey, the user would like to clear.




Sub ClearGrey()
    Dim lr As Long, i As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        If Range("B" & i).Interior.ColorIndex <> 16 Then    'This is a grey but you will need to adjust to your grey
            Range("B" & i).ClearContents
        End If
    Next i
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by AlanSidman.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use VBA code - Find value from cell in different column and multiply by another cell
How to use VBA code to ccolour tabs based on tab/text number
How to use VBA code to insert a row at the top of a table
How to use code to remove columns


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)