VBA help - Deleting and replacing conditional formatting

willow1985

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
886
Office Version
  1. 365
Platform
  1. Windows
Hello,

I need help with a VBA code. I have a workbook where users will repeatedly copy and paste in a table causing the conditional formatting to get messed up. Now there is no way to restrict this as the users need complete access and they need to copy and paste, so I was hoping to make a VBA code that will run when the workbook is closed that will clear the conditional formatting and re-enter it before save (also recalculate and turn on calculation) before save.

I am getting a syntax error on the ".FormatConditions.Add xlExpression, Formula1:" line for the conditional formatting and I do not know what I am missing (see below code)

Any help would be appreciated!

Thank you

VBA Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Worksheets("Master Forecast").Range("A3:Z")
.FormatConditions.Delete
End With
With ThisWorkbook.Worksheets("Master Forecast")
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$B3="FIRM""
.FormatConditions(1).Font.FontStyle = "Bold"
.FormatConditions(1).Font.Color = 255
.FormatConditions(1).Font.ThemeFont = xlThemeFontMinor
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$S3="COPY LINE - DO NOT DELETE""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorDarkl
.FormatConditions(1).Interior.TintAndShade = 0
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$R3="SH""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent3
.FormatConditions(1).Interior.TintAndShade = 0
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$Q3="S""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent4
.FormatConditions(1).Interior.TintAndShade = 0
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3="MASA""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent4
.FormatConditions(1).Interior.TintAndShade = 0.399945066682943
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3="COMPONENTS""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.Color = 5287936
.FormatConditions(1).Interior.TintAndShade = 0
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3="SECTIONAL""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.Color = 16737945
.FormatConditions(1).Interior.TintAndShade = 0
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3="FLIGHT""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.Color = 65535
.FormatConditions(1).Interior.TintAndShade = 0
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
With .Range("A3:Z3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3="AUGER""
.FormatConditions(1).Interior.Pattern = xlSolid
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent1
.FormatConditions(1).Interior.TintAndShade = 0.399945066682943
.FormatConditions(1).Interior.PatternTintAndShade = 0
End With
    Application.Calculation = xlAutomatic
    Application.CalculateBeforeSave = True

End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Quotes inside a formula need to be doubled up, like
VBA Code: 
Formula1:="=$B3=""FIRM"""
 
Upvote 0
Try:
VBA Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With ThisWorkbook.Worksheets("Master Forecast").Range("A3:Z3000")
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, Formula1:="=$B3=""FIRM"""
        .FormatConditions(1).Font.FontStyle = "Bold"
        .FormatConditions(1).Font.Color = 255
        .FormatConditions(1).Font.ThemeFont = xlThemeFontMinor
        .FormatConditions.Add xlExpression, Formula1:="=$S3=""COPY LINE - DO NOT DELETE"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorDark1
        .FormatConditions(1).Interior.TintAndShade = 0
        .FormatConditions(1).Interior.PatternTintAndShade = 0
        .FormatConditions.Add xlExpression, Formula1:="=$R3=""SH"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent3
        .FormatConditions(1).Interior.TintAndShade = 0
        .FormatConditions(1).Interior.PatternTintAndShade = 0
        .FormatConditions.Add xlExpression, Formula1:="=$Q3=""S"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent4
        .FormatConditions(1).Interior.TintAndShade = 0
        .FormatConditions(1).Interior.PatternTintAndShade = 0
        .FormatConditions.Add xlExpression, Formula1:="=$H3=""MASA"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent4
        .FormatConditions(1).Interior.TintAndShade = 0.399945066682943
        .FormatConditions(1).Interior.PatternTintAndShade = 0
        .FormatConditions.Add xlExpression, Formula1:="=$H3=""COMPONENTS"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.Color = 5287936
        .FormatConditions(1).Interior.TintAndShade = 0
        .FormatConditions(1).Interior.PatternTintAndShade = 0
        .FormatConditions.Add xlExpression, Formula1:="=$H3=""SECTIONAL"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.Color = 16737945
        .FormatConditions(1).Interior.TintAndShade = 0
        .FormatConditions(1).Interior.PatternTintAndShade = 0
        .FormatConditions.Add xlExpression, Formula1:="=$H3=""FLIGHT"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.Color = 65535
        .FormatConditions(1).Interior.TintAndShade = 0
        .FormatConditions(1).Interior.PatternTintAndShade = 0
        .FormatConditions.Add xlExpression, Formula1:="=$H3=""AUGER"""
        .FormatConditions(1).Interior.Pattern = xlSolid
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent1
        .FormatConditions(1).Interior.TintAndShade = 0.399945066682943
        .FormatConditions(1).Interior.PatternTintAndShade = 0
    End With
    Application.Calculation = xlAutomatic
    Application.CalculateBeforeSave = True
End Sub
In addition to Fluff's comment, this line of code had a lower case "L" at the end instead of the number 1.
VBA Code: 
 .FormatConditions(1).Interior.ThemeColor = xlThemeColorDark1
 
Upvote 0
You also need to change the formatcondition number, otherwise it's just changing the 1st condition added.
VBA Code: 
With ThisWorkbook.Worksheets("Master Forecast").Range("A3:Z3000").FormatConditions
   .Delete
   .Add xlExpression, Formula1:="=$B3=""FIRM"""
   .Item(.count).Font.FontStyle = "Bold"
   .Item(.count).Font.Color = 255
   .Item(.count).Font.ThemeFont = xlThemeFontMinor
   .Add xlExpression, Formula1:="=$S3=""COPY LINE - DO NOT DELETE"""
   .Item(.count).Interior.Pattern = xlSolid
   .Item(.count).Interior.PatternColorIndex = xlAutomatic
   .Item(.count).Interior.ThemeColor = xlThemeColorDark1
   .Item(.count).Interior.TintAndShade = 0
   .Item(.count).Interior.PatternTintAndShade = 0
End With
 
Upvote 0
That is the Formula1 or .FormatConditions(1) or BOTH?
 
Upvote 0
The formatconditions, but you can do it like I showed in post#4
 
Upvote 0
Ok I made all of the changes but the result was this:

1598028476833.png


As you can see no formats were set. The first 2 should be Red Font and the rest should be colored rows with specific colors:

1598028613970.png


Also how do I make "Stop if True" unchecked??

Here is the new VBA code:

VBA Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Worksheets("Master Forecast").Range("A3:AA3000")
.FormatConditions.Delete
End With
With ThisWorkbook.Worksheets("Master Forecast")
With .Range("B3:B3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$B3=""FIRM"""
.Item(.Count).Font.FontStyle = "Bold"
.Item(.Count).Font.Color = 255
.Item(.Count).Font.ThemeFont = xlThemeFontMinor
End With
With .Range("L3:L3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$AA3=""TRUE"""
.Item(.Count).Font.FontStyle = "Bold"
.Item(.Count).Font.Color = 255
.Item(.Count).Font.ThemeFont = xlThemeFontMinor
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$S3=""COPY LINE - DO NOT DELETE"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.ThemeColor = xlThemeColorDark1
.Item(.Count).Interior.TintAndShade = 0
.Item(.Count).Interior.PatternTintAndShade = 0
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$R3=""SH"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.ThemeColor = xlThemeColorAccent3
.Item(.Count).Interior.TintAndShade = 0
.Item(.Count).Interior.PatternTintAndShade = 0
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$Q3=""S"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.ThemeColor = xlThemeColorAccent4
.Item(.Count).Interior.TintAndShade = 0
.Item(.Count).Interior.PatternTintAndShade = 0
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3=""MASA"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.ThemeColor = xlThemeColorAccent4
.Item(.Count).Interior.TintAndShade = 0.399945066682943
.Item(.Count).Interior.PatternTintAndShade = 0
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3=""COMPONENTS"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.Color = 5287936
.Item(.Count).Interior.TintAndShade = 0
.Item(.Count).Interior.PatternTintAndShade = 0
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3=""SECTIONAL"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.Color = 16737945
.Item(.Count).Interior.TintAndShade = 0
.Item(.Count).Interior.PatternTintAndShade = 0
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3=""FLIGHT"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.Color = 65535
.Item(.Count).Interior.TintAndShade = 0
.Item(.Count).Interior.PatternTintAndShade = 0
End With
With .Range("A3:AA3000")
.Activate
.FormatConditions.Add xlExpression, Formula1:="=$H3=""AUGER"""
.Item(.Count).Interior.Pattern = xlSolid
.Item(.Count).Interior.PatternColorIndex = xlAutomatic
.Item(.Count).Interior.ThemeColor = xlThemeColorAccent1
.Item(.Count).Interior.TintAndShade = 0.399945066682943
.Item(.Count).Interior.PatternTintAndShade = 0
End With
    Application.Calculation = xlAutomatic
    Application.CalculateBeforeSave = True
End With

End Sub

Thanks guys!
 
Upvote 0
As you have multiple ranges it would be like
VBA Code: 
With ThisWorkbook.Worksheets("Master Forecast")
   With .Range("B3:B3000").FormatConditions
      .Add xlExpression, Formula1:="=$B3=""FIRM"""
      .Item(.count).Font.FontStyle = "Bold"
      .Item(.count).Font.Color = 255
      .Item(.count).Font.ThemeFont = xlThemeFontMinor
   End With
   With .Range("L3:L3000").FormatConditions
      .Add xlExpression, Formula1:="=$AA3=""TRUE"""
      .Item(.count).Font.FontStyle = "Bold"
      .Item(.count).Font.Color = 255
      .Item(.count).Font.ThemeFont = xlThemeFontMinor
   End With
   With .Range("A3:AA3000").FormatConditions
      .Add xlExpression, Formula1:="=$S3=""COPY LINE - DO NOT DELETE"""
      .Item(.count).Interior.Pattern = xlSolid
      .Item(.count).Interior.PatternColorIndex = xlAutomatic
      .Item(.count).Interior.ThemeColor = xlThemeColorDark1
      .Item(.count).Interior.TintAndShade = 0
      .Item(.count).Interior.PatternTintAndShade = 0
      .Add xlExpression, Formula1:="=$R3=""SH"""
      .Item(.count).Interior.Pattern = xlSolid
      .Item(.count).Interior.PatternColorIndex = xlAutomatic
      .Item(.count).Interior.ThemeColor = xlThemeColorAccent3
      .Item(.count).Interior.TintAndShade = 0
      .Item(.count).Interior.PatternTintAndShade = 0
      .Add xlExpression, Formula1:="=$Q3=""S"""
      .Item(.count).Interior.Pattern = xlSolid
      .Item(.count).Interior.PatternColorIndex = xlAutomatic
      .Item(.count).Interior.ThemeColor = xlThemeColorAccent4
      .Item(.count).Interior.TintAndShade = 0
      .Item(.count).Interior.PatternTintAndShade = 0
   End With
 
Upvote 0
It is highlighting the proper colors now however The 2 items with the font are still not performing the way I want them to. Currently the result for the first 2 conditions applied to columns B and L is:

1598033087673.png


1598033154220.png


How Can I fix this?

Thank you guys, this is the only part left that is not working.

Note: I grayed out sensitive information on my sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
highlight numbers
Replies
7
Views
309
MARK858
MARK858
K
  • Question
Highlight line question
Replies
2
Views
295
kerm007
K
L
  • Question
Conditional Formatting with VBA
Replies
3
Views
171
skillilea
S
F
  • Solved
VBA set conditional formatting application-defined or object-defined error
Replies
3
Views
65
Cubist
C
Nuke_It_Newport
  • Solved
Conditional Formatting Rules Not Applying
Replies
1
Views
680
Nuke_It_Newport
Nuke_It_Newport

Forum statistics

Threads
1,214,392
Messages
6,119,254
Members
448,879
Latest member
oksanana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top