基于函数的VBA中的条件格式化 [英] Conditional Formatting in VBA, based on functions
问题描述
当我设置CF来获取代码时,我尝试录制一个宏,但不记录该类型的条目。
这里是我的规则, m应用,目前在范围:= $ 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)
= A1,6)=Base(,A1 <50)
= $ B1-9.999(当前单元格小于此值)
像我说的那样,我可以通过将整个列从一张工作表复制到下一个工作表来手动复制,但是我无法将其复制到所有工作表中,我想找到一种将其作为宏的方法,因此可以轻松应用于新的工作簿(通常有10-15张)。
Sub DoCFRules()
Dim rng As Range
Set rng = ThisWorkbook.Sheets(Sheet1)。单元格
rng.Parent.Activate
rng.Cells(1)。选择重要!
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 ...
结束Sub
Sub ApplyCF(rng As Range,sFormula As String,clr As Long)
带有rng.FormatConditions.Add(Type:= xlExpression,Formula1:= sFormula)
。 Interior.Color = clr
End with
End Sub
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).
I tried recording a macro while I set up the CF to get the code, but it doesn't record that type of entry.
Here are the rules I'm applying, currently to the range: =$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).
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
这篇关于基于函数的VBA中的条件格式化的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!