Conditional Formatting in VBA, based on functions
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