My macro to delete columns with red cells does not seem to work with the conditional formatting red

Question

so I made this code to delete all the columns that have a interior red cells in row 18. The code works.

The problem is it does not work when I conditional format the cells in that row. What I planned to do was conditional format the row then use the macro to quickly clear the columns with the red cells.

It works when I fill in a cell with red but not when I use conditional formatting. It seems like it should be the same red in conditional formatting.

 Sub sbDelete_Columns_Based_On_Cell_Color()
    Dim lColumn As Long
    Dim iCntr As Long

    lColumn = 50
    For iCntr = lColumn To 1 Step -1
    If Cells(18, iCntr).Interior.Color = Excel.XlRgbColor.rgbRed Then
      Columns(iCntr).Delete
    End If
    Next iCntr
    End Sub

Your conditional formatting rule is using some kind of logic to highlight cells red so just use that exact same logic here. You are using conditional formatting as a middle man. Skip the middle man, code your logic here in VBA — urdearboy

Error 1004 Error 1004 · Accepted Answer · 2019-02-15T18:12:17

Using the below code you can scrap cell color from conditional formatting:

Option Explicit

Sub test()

    Dim Color As Long

    With ThisWorkbook.Worksheets("Sheet2")

        Color = .Range("D1").DisplayFormat.Interior.Color

    End With

End Sub

My macro to delete columns with red cells does not seem to work with the conditional formatting red

1 Answers