Home

Conditional Fomatting in a macro

%3CLINGO-SUB%20id%3D%22lingo-sub-884349%22%20slang%3D%22en-US%22%3EConditional%20Fomatting%20in%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-884349%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can%20create%20a%20manual%20Conditional%20formatting%20rule%20to%20do%20what%20I%20want.%26nbsp%3B%20Rule%20is%20that%20column%20A%20includes%20%22Total%22.%26nbsp%3B%20and%20I%20want%20all%20the%20subtotal%20lines%20to%20be%20shaded%20as%20a%20break%20between%20projects.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20373px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134402i285CEFD84F6ED5C7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%2009-30-19%20at%2005.44%20PM.JPG%22%20title%3D%22Screen%20Shot%2009-30-19%20at%2005.44%20PM.JPG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20625px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134407iAFA62F896C72BDC4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%2009-30-19%20at%2005.45%20PM.JPG%22%20title%3D%22Screen%20Shot%2009-30-19%20at%2005.45%20PM.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20Rule%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESet%20area%20and%20apply%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20841px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134433iBAEFC40CF1835E89%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%2009-30-19%20at%2005.49%20PM.JPG%22%20title%3D%22Screen%20Shot%2009-30-19%20at%2005.49%20PM.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdds%20all%20the%20formatting%20I%20want.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20when%20I%20record%20the%20Macro%2C%20the%20conditional%20formatting%20does%20not%20record...%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EAnyone%20know%20what%20the%20code%20would%20be%20to%20add%20it%20manually%20so%20that%20we%20don't%20have%20to%20create%20the%20conditional%20formatting%20every%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-884349%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885116%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Fomatting%20in%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417739%22%20target%3D%22_blank%22%3E%40Mark97348%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGoogled%20around%20a%20bit%2C%20found%20some%20code%20(%3CA%20href%3D%22http%3A%2F%2Fstackoverflow.com%2Fquestions%2F13661965%2Fddg%2313664575%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EConditional%20Formatting%20using%20Excel%20VBA%20codehttp%3A%2F%2Fstackoverflow.com%2Fquestions%2F13661965%2Fddg%2313664575%3C%2FA%3E)%26nbsp%3Band%20tweaked%20it%20a%20bit.%20Try%20this%20code%20in%20your%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESub%20setCondFormat()%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3ERange(%22A1%22).Select%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3EWith%20Range(%22A%3AH%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E.FormatConditions.Add%20Type%3A%3DxlExpression%2C%20Formula1%3A%3D%20_%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%22%3Dif(isnumber(search(%22%22Total%22%22%2C%24A1))%2Ctrue)%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3EWith%20.FormatConditions(.FormatConditions.Count)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E.SetFirstPriority%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3EWith%20.Interior%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E.PatternColorIndex%20%3D%20xlAutomatic%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E.Color%20%3D%205287936%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E.TintAndShade%20%3D%200%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3EEnd%20With%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3EEnd%20With%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3EEnd%20With%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887023%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Fomatting%20in%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20very%20much%20for%20the%20response.%26nbsp%3B%20I%20found%20the%20stack%20overflow%20code%20in%20a%20couple%20of%20my%20google%20searches%20yesterday%20as%20well.%20But%20I%20kept%20getting%20errors%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20the%20code%20you%20posted.%26nbsp%3B%20I%20am%20getting%20a%20%22Compile%20error%3A%26nbsp%3B%20Syntax%20error%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20tinker%20with%20it%20a%20bit%20more%20to%20see%20if%20I%20can%20find%20a%20working%20syntax.%26nbsp%3B%20Just%20not%20sure%20what%20Visual%20Basic%20doesn't%20like%20about%20the%20syntax...%26nbsp%3B%20I%20have%20seen%20very%20similar%20code%20posted%20and%20others%20online%20indicate%20it%20will%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20still%20missing%20something.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20900px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134978iC7F9158AEF956054%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%2010-01-19%20at%2011.29%20AM.JPG%22%20title%3D%22Screen%20Shot%2010-01-19%20at%2011.29%20AM.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888300%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Fomatting%20in%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417739%22%20target%3D%22_blank%22%3E%40Mark97348%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttaching%20a%20workbook%20that%20contains%20the%20macro.%20It's%20working%20on%20my%20system.%20I'm%20on%20a%20Mac%20but%20that%20shouldn't%20matter.%20Tried%20to%20replicate%20your%20error%20but%20did%20not%20succeed.%20Get%20all%20sorts%20of%20compile%20errors%20but%20never%20a%20%22syntax%20error%22.%20Perhaps%20somebody%20else%20can%20figure%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890166%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Fomatting%20in%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890166%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20code%20and%20for%20trying.%26nbsp%3B%20Anyone%20else%20have%20ideas%20to%20try%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20an%20explanation%20on%20why%20conditional%20formatting%20will%20not%20record%20as%20part%20of%20a%20macro%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMark%3C%2FP%3E%3C%2FLINGO-BODY%3E
Mark97348
New 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies