Clear Red Cells with VBA

[Gerry P]

I am totally new to this.....But I need help with a macro that will clear cells that are high lighted Red

I am trying to make a line plot with the combined good values from columns x,y,z x5:z21000

Excel 2013 Red color is rgb 255 Sheet name is " Curve Data "


I could email file if needed................. Thanks in advance for any help

 

[JoeMo]

Are the cells highlighted by filling them red manually or are they filled using conditional formatting?

 

[My Aswer Is This]

Try this:

Sub Clear_Red_Cells()
Dim c As Range

    For Each c In Sheets("Curve Data").UsedRange
        If c.Interior.Color = RGB(255, 0, 0) Then c.Value = Isnothing
    Next

End Sub

 

[Gerry P]

Are the cells highlighted by filling them red manually or are they filled using conditional formatting?

Conditional formatting..

 

[Gerry P]

Nope they are still there...mmmmm

 

[My Aswer Is This]

You did not answer the question. How did they become Red?

Was it do to conditional formatting.

And do you want the text and the coloring removed or just the text.
And when you say: highlighted you mean the cells interior color correct? Or do you mean font color is red.

 

[Gerry P]

-- removed inline image ---

 

[Gerry P]

I have the cells conditional formatted that if the formula result is #N/A the whole Cell will become red

i tried to post an image and made a mess below oooops.

 

[MARK858]

You did not answer the question. How did they become Red?

Was it do to conditional formatting.

See post #4

@Gerry P try....

Sub Clear_Red_Cells()
Dim c As Range

    For Each c In Sheets("Curve Data").Range("X5:Z21000")

        If c.DisplayFormat.Interior.Color = RGB(255, 0, 0) Then c.Value = ""
    Next

End Sub

 

[MARK858]

Seeing as you are trying to clear formulas with a #N/A result then as long as you have no other errors you would be better off using the code below.

Sub Clear_Red_Cells2()
Sheets("Curve Data").Range("X5:Z21000").SpecialCells(-4123, 16) = ""
End Sub