How to use VBA code to clear cells based on specific criteria
Category: [Excel] Demo Available
How to use VBA code to clear cells based on specific criteria
Requirement:
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.
Solution:
Code:
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.