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

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"""

"Cathy" wrote:

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

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("qryHeirarchyCompaniesD etail").Bold = True

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

Thanks,
Cathy

"Joel" wrote:

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"""

"Cathy" wrote:

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

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

"Cathy" wrote:

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("qryHeirarchyCompaniesD etail").Bold = True

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

Thanks,
Cathy

"Joel" wrote:

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"""

"Cathy" wrote:

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