Sep 30 2019 05:57 PM
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.
Sep 30 2019 11:10 PM
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
Oct 01 2019 11:36 AM
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.
Oct 02 2019 01:29 AM
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!
Oct 02 2019 04:53 PM
Thanks for the code and for trying. Anyone else have ideas to try?
Or an explanation on why conditional formatting will not record as part of a macro?
Thanks!
Mark