Formula Conditional Formating

[Guest]

I need VBA to perform some conditional formatting on a spreadsheet that is
overlaid by ACCESS. I can get the formula to work in the tradional
conditional formatting by using the Formula: =RIGHT($E1,5)='Total', then
changing the format to Bold/Italic. I have tried numerous ways to make it
work in VBA, but I'm afraid I just don't understand enough about VBA to
figure it out. Here's my latest attempt:

Range("E1:K61000").Select
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=RIGHT($E1,5)='Total'"
Selection.FormatConditions(1).Bold = True
Selection.FormatConditions(1).Italic = True

It keeps rejecting the second & third line of code. Any suggestions?

Thank you in advance,
Cathy

 

[Guest]

Use either of these formulas

Range("E1:K61000").Select
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=find(RIGHT($E1,5),""Total"")>0"

or

Range("E1:K61000").Select
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=RIGHT($E1,5)=""Total"""

 

[Guest]

Joel,

Your first suggestion allows the code to get past the first few lines... now
it stops on the Bold statement. The name of my tab is:
qryHeirarchyCompaniesDetail. So I've tried:

Range("E1:K61000").Select
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=find(RIGHT($E1,5),""Total"")>0"
Selection.FormatConditions("qryHeirarchyCompaniesDetail").Bold = True

But it still fails. Now what am I doing wrong?

Thanks,
Cathy

 

[Guest]

I performed a learn macro and then made the format changes. try this. The 1
can be either 1 or 2 or 3 for the three different conditional formating
statements.


Range("E1:K61000").Select
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=find(RIGHT($E1,5),""Total"")>0"

With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With