Formula Conditional Formatting

[Guest]

I need to produce some VBA for a Formula Conditional Formatting. The
spreadsheet I need use the VBA on is overlaid by ACCESS, so using the
traditional conditional formatting will not work. I can however get the
desired results using the dropdown Conditional Formatting, choosing Formula
Is and entering =RIGHT($E39,5)="Total" and Bold/Italic.

I'm very new to VBA, and my latest attempt is:

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

Run time is rejecting the second & third lines. Any suggestions?

Thank you,
Cathy

 

[Guest]

Try something like this:

With Range("E1").FormatConditions _
.Add(Type:=xlExpression, Formula1:="=RIGHT($E1,5)=" & """" & "Total" &
"""")
With .Font
.Bold = True
.Italic = True
End With
End With
Range("E1").Copy
Range("E1:K61000).PasteSpecial xlPasteFormats
Application.CutCopyMode = False

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron,

The bold and italic work perfectly. Unfortunately it is also picking up the
grey background that ACCESS assigns the title bar and making the entire area
grey.

I've tried changing all of the E1's to E2, but then the bold and italic move
up a row.

Any suggestions on how I can either prevent the grey from occuring, or
change the background back to white afterwards?

Thank you!
Cathy

 

[Guest]

Yeah......The COPY part of that procedures migrates the heading format.

Maybe this?:

With Range("E2")
.Select
With .FormatConditions _
.Add(Type:=xlExpression, Formula1:="=RIGHT($E2,5)=" & """" & "Total" &
"""")
With .Font
.Bold = True
.Italic = True
End With 'font
End With 'cf cond
End With 'range
Range("E2").Copy
Range("E2:K61000").PasteSpecial xlPasteFormats
Application.CutCopyMode = False

Note: I'm no fan of using SELECT, but the Conditional Formatting process is
being particularly obstinate in ruining the CF if the active cell is below
row 2. Perhaps somebody else has a way to avoid that (without looping
through each cell).

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

That works perfect!

Thank you so much,
Cathy

Yeah......The COPY part of that procedures migrates the heading format.

Maybe this?:

With Range("E2")
.Select
With .FormatConditions _
.Add(Type:=xlExpression, Formula1:="=RIGHT($E2,5)=" & """" & "Total" &
"""")
With .Font
.Bold = True
.Italic = True
End With 'font
End With 'cf cond
End With 'range
Range("E2").Copy
Range("E2:K61000").PasteSpecial xlPasteFormats
Application.CutCopyMode = False

Note: I'm no fan of using SELECT, but the Conditional Formatting process is
being particularly obstinate in ruining the CF if the active cell is below
row 2. Perhaps somebody else has a way to avoid that (without looping
through each cell).

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks for the feedback.
I'm glad that worked for you, Cathy


***********
Regards,
Ron

XL2002, WinXP