Forum Discussion
Conditional Fomatting in a macro
I can create a manual Conditional formatting rule to do what I want. Rule is that column A includes "Total". and I want all the subtotal lines to be shaded as a break between projects.
Create Rule
Set area and apply
Adds all the formatting I want.
However, when I record the Macro, the conditional formatting does not record...
Anyone know what the code would be to add it manually so that we don't have to create the conditional formatting every time.
4 Replies
Googled around a bit, found some code (Conditional Formatting using Excel VBA codehttp://stackoverflow.com/questions/13661965/ddg#13664575) and tweaked it a bit. Try this code in your macro.
Sub setCondFormat()
Range("A1").Select
With Range("A:H")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=if(isnumber(search(""Total"",$A1)),true)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
End With
End With
End Sub
Thanks very much for the response. I found the stack overflow code in a couple of my google searches yesterday as well. But I kept getting errors
I tried the code you posted. I am getting a "Compile error: Syntax error"
I will tinker with it a bit more to see if I can find a working syntax. Just not sure what Visual Basic doesn't like about the syntax... I have seen very similar code posted and others online indicate it will work.
I am still missing something.
Attaching a workbook that contains the macro. It's working on my system. I'm on a Mac but that shouldn't matter. Tried to replicate your error but did not succeed. Get all sorts of compile errors but never a "syntax error". Perhaps somebody else can figure it out.
Good luck!
