Deleting rows using interior color.index value

chadski778

Active Member
Joined
Mar 14, 2010
Messages
297
I would like code to delete all rows where the cell fill interior color.index values in column B is not 3. This would leave just the rows with red formatted cells (interior color.index = 3) in column B. Is this possible?
Many thanks
Chad
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
chadski778,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider.

I assume that the raw data worksheet is Sheet1. You can change the worksheet name in the macro.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub DeleteColumnB_NOT_InteriorColorIndex_3()
' hiker95, 05/03/2015, ME852780
Dim r As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  For r = lr To 1 Step -1
    If .Cells(r, 2).Interior.ColorIndex <> 3 Then .Rows(r).Delete
  Next r
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteColumnB_NOT_InteriorColorIndex_3 macro.
 
Upvote 0
My last reply didnt send for some reason? Your macro deleted all rows so I am assuming I have the wrong interior color.index for my formatted cell. How would I find out the correct value for the red second from bottom left on the colour palette? The cells I want to keep are also in bold so perhaps deleting all lines wthout Bold text in column B may be an easier option? I have Excel 2010 and windows Vista
 
Upvote 0
How would I find out the correct value for the red second from bottom left on the colour palette?
Select a cell with the red color you are talking about and then execute this line of code in the VB editor's Immediate Window (CTRL+G if I you do not see it)...

? Selection.Interior.ColorIndex

Use the number that prints out in place of the 3 in hiker95's code.
 
Upvote 0
I get interior color.index = 3 for a normal cell with red fill and -4142 with it conditionally formatted with the same colour? My cells are conditionally formatted so I need to use -4142 but it doesnt work in the macro hiker95 sent me. It may be easier to use code that deletes all the rows that contain non-bold text in column B instead because my formatting uses Bold text. The idea is to produce a summary of all the rows that contain conditional formatting that have exceeded a threshold and therefore have turned red and bold
 
Upvote 0
Actually all the conditionally formatted cells are interior color.index -4142 even the ones that contain values that haven't exceeded a trigger value and are not red so that method won't work. Maybe deleting the non-bold text may work
 
Upvote 0
chadski778,

Maybe deleting the non-bold text may work

Here is another macro for you to consider.

Sample raw data in worksheet Sheet1 (you can change the sheet name in the macro):


Excel 2007
B
1A
2B
3not bold
4D
5not bold
6F
7not bold
8H
9
Sheet1


After the new macro:


Excel 2007
B
1A
2B
3D
4F
5H
6
7
8
9
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub DeleteColumnB_nontextbold()
' hiker95, 05/03/2015, ME852780
Dim r As Long, lr As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  For r = lr To 1 Step -1
    If .Cells(r, 2).Font.FontStyle <> "Bold" Then .Rows(r).Delete
  Next r
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteColumnB_nontextbold macro.
 
Upvote 0
That works with normal bold text but I think the conditional formatting is screwing it up. I can think of one method that may work. I could try to delete all rows where the value in column B is less than the corresponding value in column C (so if B2 is less than C2 delete row). I wish it was straightforward but there is
an exception for B9-B20 where I want to delete the rows where the value in B is larger than the value in C. Don't know if that's possible but thanks for your help all the same
 
Upvote 0
chadski778,

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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