|
Formula Conditional Formatting
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 |
Formula Conditional Formatting
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 "Cathy" wrote: 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 |
Formula Conditional Formatting
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 "Ron Coderre" wrote: 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 "Cathy" wrote: 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 |
Formula Conditional Formatting
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 "Cathy" wrote: 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 "Ron Coderre" wrote: 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 "Cathy" wrote: 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 |
Formula Conditional Formatting
That works perfect!
Thank you so much, Cathy "Ron Coderre" wrote: 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 "Cathy" wrote: 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 "Ron Coderre" wrote: 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 "Cathy" wrote: 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 |
Formula Conditional Formatting
Thanks for the feedback.
I'm glad that worked for you, Cathy *********** Regards, Ron XL2002, WinXP "Cathy" wrote: That works perfect! Thank you so much, Cathy "Ron Coderre" wrote: 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 "Cathy" wrote: 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 "Ron Coderre" wrote: 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 "Cathy" wrote: 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 |
| All times are GMT +1. The time now is 04:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com