用于删除条件格式的宏填充颜色为红色

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

我有一份很大的excel报告，我正在努力让我的技术人员过得更轻松。

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.

我有一个单元格，如果一个数字不在另一个单元格的10%以内，那么这个单元格就会用红色高亮显示。我的问题是，我不希望我的客户看到我报告中的红色高亮显示。我必须把我的excel报告转换成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

如有任何帮助，我们将不胜感激。谢谢

3 个解决方案

#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.

答案是肯定的。首先，你需要确定你正在研究的范围。然后检查您的范围是否有格式，如果有，删除，否则应用(如切换)。下面的代码就是这么做的。改变我的评论和调整。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.

重要:您可以在任何范围内分配条件格式，但它将应用于我在这里讨论的ModifyAppliedToRange中设置的内容。

#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.

您可以使用这个来清除页面上任何被涂成红色(255)的单元格。不确定这是不是你所需要的。

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?

回到红色，你不能运行你已经有的宏吗?

#3

Try this:

试试这个:

Selection.FormatConditions.Delete