Deleting rows using interior color.index value

[chadski778]

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

 

[Rick Rothstein]

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

Just so you know, there is a difference between format (color, boldness, etc.) applied manually as opposed to applied by Conditional Formatting... the manual formats can be seen directly, the Conditional Formatted ones cannot. So, using your above stated rule, see if this macro works for you...

Sub DeleteRows()
  Dim LR As Long
  LR = Cells(Rows.Count, "B").End(xlUp).Row
  Range("B2:B" & LR) = Evaluate(Replace("IF((ROW(B2:B@)>8)*(ROW(B2:B@)" & _
                                "< 21),IF(B2:B@>C2:C@,""#N/A"",B2:B@),IF(" & _
                                "B2:B@< C2:C@,""#N/A"",B2:B@))", "@", LR))
  Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[chadski778]

Rick, thank you that has worked brilliantly

 

[chadski778]

Rick,
After running your macro I have the formatted cells that I want but also rows containing 0's but I think these correspond to the empty cells in my range of data in column B that existed before I ran your macro. Could you please amend the macro to remove the rows containing 0's in column B as well which would leave just the conditionally formatted cells?

Many thanks