Use a macro to highlight cells with a background colour

user_15315939 0
Viewed 45

I need my macro to delete rows containing a red cell.

I started by sorting the column containing red cells so that blank cells were at the top, then manually clicked the top red cell (row 100) and highlighted the rest using Ctrl-Shift-Down. As such, part of my code looks like this:

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add2 _
    Key:=Range("Table1[[#All],[First Name]]"), SortOn:= _
    xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=RGB _
    (255, 0, 0), Operator:=xlFilterCellColor
Rows("100:150").Select

How can I amend the last part so that my macro detects the red cells then highlights them, so that it'll work regardless of how many cells I have in my sheet?

Thanks in advance.

fill vba excel
1 Answers

The following code should give you what you want. Uses AutoFilter by cell color & deletes en masse. Let me know how you go with it.

Option Explicit
Sub DeleteByColor()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws.ListObjects("Table1").Range
    .AutoFilter 1, RGB(255, 0, 0), 8
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter
End With

End Sub
Admin1
Related