Remove row with Color Condition VBA

[Bernz]

Hi ,

Good day!

I have a problem regarding with the cell color and color in conditional formatting.
If I manually color the cell the code will work and remove the entire row. but if I apply color condition (conditional formatting), It would not remove the row. please see my code below.

Sub deleterow()
Dim i As Long, j As Long
Dim delRange As Range
Dim table1 As Range


Set table1 = Sheet18.Range("b2").CurrentRegion

With table1
For i = 3 To 100 '<~~ Row 7 to 300
For j = 4 To 7 '<~~ Col A to AI
If .Cells(i, j).Interior.ColorIndex = 3 Then
If delRange Is Nothing Then
Set delRange = .Cells(i, j)
Else
Set delRange = Union(delRange, .Cells(i, j))
End If
Exit For
End If
Next j
Next i
End With

If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

the checking of the data would start from column D to column G, if it can see color red or no-fill condition, it will remove the entire row including column b and C. but if the checking can detect Yellow, It wont delete the row event it has red and no-fill condition.


Hope for you response.

Thanks,

 

[Marc L]

Hi !

Thanks to edit your post and use code tags (or via code icon) !

A must see : Conditional Formatting Colors …
​

 

[Chihiro]

Depending on version of Excel, you have option to use DisplayFormat.Interior.ColorIndex (I believe Excel 2010 onward).

However, do note that while this works perfectly in code/module, it won't work in UDF when used in worksheets (much like SpecialCells property).