Macro for removing conditional formatting fill color Red

Question

I have a large excel report and I am trying to make life easier for my technicians.

I have cells where if a number is not within 10% of another cell then that cell will highlight in RED. My problem is I do NOT want my customers to see the red highlight in my report. I have to convert my excel reports to PDF.

Is there a Macro code that would only take out the conditional formatting for highlight RED? I have other conditional formatting in my report and that's why I cannot use the Macro code that deletes all conditional formatting.

Also, is there a Macro code that will make it go back to RED?

Any help on this is appreciated. Thanks

Answer 1

The answer is yes. First you need to identify the range you're working on.
Then check if your range has formatting, delete if yes, apply otherwise (like a toggling).
Below code does just that. Change the lines I've commented and adapt. HTH.

Sub ColorUnColor()
    Dim myformat As String
    Dim cfr As Range

    myformat = "=A1<(0.1*B1)" '~~> change to suit

    With Sheet2 '~~> change to suit
        Set cfr = .Range("D1:D10") '~~> change to suit
        If cfr.FormatConditions.Count = 0 Then
            .Range("A1").FormatConditions.Add xlExpression, , myformat
            With .Range("A1").FormatConditions(1)
                .Interior.Color = RGB(255, 0, 0)
                .ModifyAppliesToRange cfr
            End With
        Else
            cfr.FormatConditions.Delete
        End If
    End With
End Sub

Result:

Important: You can assign conditional formatting in any Range, but it will be applied to what you set in ModifyAppliedToRange which I discussed in here.

Answer 2

You could use this to clear any cell on the sheet that is colored red (255). Not sure if that's exactly what you need.

Sub testes()

Dim SrchRng As Range
Set SrchRng = ActiveSheet.UsedRange

    For Each Source In SrchRng
        If Source.Interior.Color = 255 Then
            Source.Interior.Color = xlNone
        End If
    Next Source

End Sub

As for going back to red couldn't you just run the macro you already have?

Answer 3

Try this:

Selection.FormatConditions.Delete