Conditional Fomatting in a macro

Copper Contributor

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.

 

Screen Shot 09-30-19 at 05.44 PM.JPGScreen Shot 09-30-19 at 05.45 PM.JPG

 

 

Create Rule

 

 

 

 

 

 

 

 

 

 

 

Set area and apply

Screen Shot 09-30-19 at 05.49 PM.JPG

 

 

 

 

 

 

 

 

 

 

 

 

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

@Mark97348 

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

 

@Riny_van_Eekelen 

 

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.

 

Screen Shot 10-01-19 at 11.29 AM.JPG

@Mark97348 

 

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!

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