VBA中的条件格式，基于函数

I have some (semi) complex conditional formatting to do in Excel. I can get it working no problem using the menu, but I want to do it programmatically because I want it applied across multiple sheets (copy, paste as format does not work).

我在Excel中有一些(半)复杂的条件格式。我可以使用菜单让它工作没有问题,但我想以编程方式进行,因为我希望它应用于多个工作表(复制,粘贴格式不起作用)。

I tried recording a macro while I set up the CF to get the code, but it doesn't record that type of entry.

我在设置CF以获取代码时尝试录制宏,但它不记录该类型的条目。

Here are the rules I'm applying, currently to the range: =$1:$65536

以下是我正在应用的规则,目前在以下范围内:= $ 1:$ 65536

=OR(COLUMN()=1,COLUMN()=2,ISBLANK($B1))
=AND(LEFT($A1,6)="Base (",A1>100)
=AND(LEFT($A1,6)="Base (",A1>=75,A1<=100)
=AND(LEFT($A1,6)="Base (",A1>=50,A1<75)
=AND(LEFT($A1,6)="Base (",A1<50)
=$B1-9.999  (current cell is less than this value)

Like I said, I can copy this manually by copying a whole column from one sheet to the next, but I can't copy it across all sheets, and I'd like to find a way to do it as a Macro so it can easily be applied to new workbooks (which tend to have 10-15 sheets).

就像我说的那样,我可以通过将整个列从一个工作表复制到另一个工作表来手动复制,但是我不能将它复制到所有工作表中,我想找到一种方法将其作为宏来实现它所以它可以很容易应用于新的工作簿(往往有10-15张)。

1 个解决方案

#1

Sub DoCFRules()

    Dim rng As Range
    Set rng = ThisWorkbook.Sheets("Sheet1").Cells

    rng.Parent.Activate
    rng.Cells(1).Select 'Important!

    ApplyCF rng, "=OR(COLUMN()=1,COLUMN()=2,ISBLANK($B1))", RGB(0, 255, 0)
    ApplyCF rng, "=AND(LEFT(A1,6)=""Base ("",A1>100)", RGB(255, 0, 0)
    'etc...

End Sub

Sub ApplyCF(rng As Range, sFormula As String, clr As Long)
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:=sFormula)
        .Interior.Color = clr
    End With
End Sub