Delete Table Rows by Cell Interior Color

zero269

Active Member
Joined
Jan 16, 2023
Messages
253
Office Version
  1. 365
Platform
  1. Windows
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:

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:
VBA Code:
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:
1676498842728.png


Sample Data:
VBA Testing.xlsm
ABC
1IDAuthorSeries
2100Dr. SeussBerenstain Bears
3101Larrison, Joanne
4102Lucado, Max
5103Mayer, MercerCorduroy
6104Rey, MargretZonderkidz
7105Sendak, MauriceDr. Seuss
8106Willems, Mo
Delete Table Rows
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:C8,E2:G3Expression=SUM(COUNTIF(A2,"*"&lstPreferred&"*"))textNO


Here's what I was hoping would happen:

1676498969997.png


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,
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Also I was not able to make it work with RGB values. A slightly modified version:
VBA Code:
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.
 
Upvote 0
Also I was not able to make it work with RGB values. A slightly modified version:
VBA Code:
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...
VBA Code:
If myRange(i).DisplayFormat.Interior.Color = 38 Then
Didn't have any effect on the code; no errors either.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Solution
Here check the sample file:

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

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top