Delete Table Rows by Cell Interior Color

[zero269]

Hello,

I'm trying to delete some Table Rows based on a cells Interior Color as applied by a Conditional Formatting rule.
The CF is applied to two columns (B:C) in my Test Workbook.

I came across two threads that are trying to do something similar:

Macro to delete rows based on cell color

I created the following macro to delete all rows that have a cell background color of Yellow in Column D Sub deleterow() Dim myRange as Range Dim cell As Range Application.Calculation = xlCalculationManual Set myRange = Worksheets(1).Range("D2:D1200") For Each cell In...

www.mrexcel.com

Conditional Formatting issues with VBA insert/delete row

Hi, I’m hoping someone can help…I’ve read through quite a lot of threads and see that what I’m struggling with seems like a common thing…but I still can’t figure it out….I think my mind is zonked as I’m not a regular Excel user. Quick outline: I'm struggling to add/keep conditional formatting...

www.mrexcel.com

The first one is closer to what I'm trying to do, while the 2nd thread is far too complex for me to decipher.

What I'm working with:

Sub DeleteTableRowsByColor()
' Delete Table Rows based on Cell Interior Color (B:C)

     Dim myRange As Range
     Dim i As Range

    Application.Calculation = xlCalculationManual
    Set myRange = Worksheets(1).Range("B2:C8")
    
    For i = myRange.Rows.Count To 1 Step -1
        If myRange(i).Interior.ColorIndex = RGB(255, 199, 206) Then
        myRange(i).EntireRow.Delete
        End If
    Next i

    Application.Calculation = xlCalculationAutomatic
    
End Sub

As above, I get a compile error:

Sample Data:

VBA Testing.xlsm
	A	B	C
1	ID	Author	Series
2	100	Dr. Seuss	Berenstain Bears
3	101	Larrison, Joanne
4	102	Lucado, Max
5	103	Mayer, Mercer	Corduroy
6	104	Rey, Margret	Zonderkidz
7	105	Sendak, Maurice	Dr. Seuss
8	106	Willems, Mo
Delete Table Rows

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C8,E2:G3	Expression	=SUM(COUNTIF(A2,"*"&lstPreferred&"*"))	text	NO

Here's what I was hoping would happen:

The OP from that above thread indicated everything worked as is... but that doesn't seem to be the case unless I didn't make the appropriate changes.
I'm using RGB values, while he was using just a value of '3'.

Thank you,

 

[Legacy 143009]

Also I was not able to make it work with RGB values. A slightly modified version:

Sub DeleteTableRowsByColor()
' Delete Table Rows based on Cell Interior Color (B:C)

  Dim myRange As Range
  Dim i As Long

  Application.Calculation = xlCalculationManual
  Set myRange = Worksheets(1).Range("B2:C8")
   
  For i = myRange.Cells.Count To 1 Step -1
    If myRange(i).Interior.ColorIndex = 38 Then
      myRange(i).EntireRow.Delete
    End If
  Next i

  Application.Calculation = xlCalculationAutomatic
   
End Sub

i should be a Long integer instead of range type. Also you should define cells count instead of row count.

 

[zero269]

Also I was not able to make it work with RGB values. A slightly modified version:

Sub DeleteTableRowsByColor()
' Delete Table Rows based on Cell Interior Color (B:C)

  Dim myRange As Range
  Dim i As Long

  Application.Calculation = xlCalculationManual
  Set myRange = Worksheets(1).Range("B2:C8")
  
  For i = myRange.Cells.Count To 1 Step -1
    If myRange(i).Interior.ColorIndex = 38 Then
      myRange(i).EntireRow.Delete
    End If
  Next i

  Application.Calculation = xlCalculationAutomatic
  
End Sub

i should be a Long integer instead of range type. Also you should define cells count instead of row count.

Hi Flashbond,

Thanks for looking into this for me.

To test your code as written, I looked up the RGB values for Color Index 38 (RGB: 255,153,204) and used those values for my Conditional Formatting Rule (CFR).
Unfortunately, after changing the CFR color to match, it didn't remove any of the Table Rows that contained the conditionally formatted cells.
The code didn't throw up any errors, so I can only assume that it has something to do with the Interior Color not being identified. I stumbled upon the DisplayFormat that I've seen others reference, but replacing...

If myRange(i).Interior.ColorIndex = 38 Then

with...

If myRange(i).DisplayFormat.Interior.Color = 38 Then

Didn't have any effect on the code; no errors either.

 

[Legacy 143009]

I used the same color code in your XL2BB example.

 

[zero269]

Unfortunately, I'm unable to delete the Table Rows and not Sheet Rows based on the Conditionally Formatted cell color.
What I've found is that dealing with colors is different when it's highlighted manually and using a Conditional Format Rule (CFR).

Interior.Color
Interior.ColorIndex (a limited set of colors)
DisplayFormat.Interior.Color This one appears to be the correct one, but unable to get the Table Row to delete

I've tried Rows.Delete but not having any luck.

One thing to note is that I cannot delete the Entire (Sheet) Row as there are two Tables on the sheet this will be used. Left table uses a Web Query and the Results are placed to the right of it.

 

[zero269]

Unfortunately, I'm unable to delete the Table Rows and not Sheet Rows based on the Conditionally Formatted cell color.
What I've found is that dealing with colors is different when it's highlighted manually and using a Conditional Format Rule (CFR).

Interior.Color
Interior.ColorIndex (a limited set of colors)
DisplayFormat.Interior.Color This one appears to be the correct one, but unable to get the Table Row to delete

I've tried Rows.Delete but not having any luck.

One thing to note is that I cannot delete the Entire (Sheet) Row as there are two Tables on the sheet this will be used. Left table uses a Web Query and the Results are placed to the right of it.

Just wanted to share a solution for this Thread where I created some code that was working, it was resulting in a runtime error.

There are two solutions in another thread. The first one addressed a single column, while the second one addresses multiple columns. They are both great solution and both worked in my testing:
Single Column Solution by HaHoBe: VBA: Run-time error '1004' Trying to Delete "Table" Rows
Double Column Solution by Domenic: VBA: Run-time error '1004' Trying to Delete "Table" Rows

 

[Legacy 143009]

Here check the sample file:

Sample.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

I don't share the code because it is exactly the same with the code in post#2.