Replacing Colored Cell with a Value in VBA

[Schneider]

Hello,

I have the following code that looks at column B and replaces all the cells that are zero with blank and then deletes the row between the given range that are blank. My next step would be to select all the colored cells in column B and either delete the row or change the current value to blank. Therefore once blank the row will be deleted.

I am trying unsuccessfully to acheive the next step by the code in red. Does someone know of a code that will do this?

Sheets("Template").Select
Columns("B:B").Replace 0, "", xlWhole
Columns("B:B").Replace Interior.ColorIndex = 3, "", xlWhole
On Error Resume Next
Range("B20:B31").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange


Thank you.

 

[JamesW]

Hi,

To remove the value:

    lRow = Range("B1").End(xlDown).Row
    For i = 1 To lRow
        If Cells(i, 2).Interior.ColorIndex = 3 Then Cells(i, 2).Value = ""
    Next

To delete the row:

    lRow = Range("B65536").End(xlUp).Row
    For i = lRow To 1 Step -1
        If Cells(i, 2).Interior.ColorIndex = 6 Then Cells(i, 2).EntireRow.Delete
    Next

 

[Schneider]

Thank you James.