VBA help - Deleting and replacing conditional formatting

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
AA3 is an index match formula pulling from another table. when there is a Job number match it will pull rather the word "TRUE" or "FALSE" or leave it blank

1598034314601.png
 
Upvote 0
Ok, in that case the formula would be
VBA Code:
      .Add xlExpression, Formula1:="=$AA3=TRUE"
Otherwise it's looking for a text value, rather than a logical value.
 
Upvote 0
That worked for column L, but is there any way to make it stop making the cells have a white fill? I only want the font affected.

When I do the conditional formatting rules manually I get:

1598035478548.png


However when I do the conditional formatting with VBA I get:

1598035512944.png


Thank you, I am still very new to conditional formatting...First attempt with VBA actually
 
Upvote 0
Ok I figured it out, I need to turn off Stop is True. I added a line to the bottom of the code:

With ThisWorkbook.Worksheets("Master Forecast").Range("A3:AA3000").FormatConditions.StopIfTrue = False
End With

in an attempt to turn it off for all but it doesn't like that.

How would I write this last line?

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").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
End With

With .Range("A3:AA3000").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").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").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").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").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").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").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

With ThisWorkbook.Worksheets("Master Forecast").Range("A3:AA3000").FormatConditions.StopIfTrue = False
End With
    Application.Calculation = xlAutomatic
    Application.CalculateBeforeSave = True
End With


End Sub
 
Upvote 0
Got it!
StopIfTrue has to be on each format.

Thanks guys!

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").FormatConditions
.Add xlExpression, Formula1:="=$B3=""FIRM"""
.Item(.Count).Font.FontStyle = "Bold"
.Item(.Count).Font.Color = 255
.Item(.Count).Font.ThemeFont = xlThemeFontMinor
.Item(.Count).StopIfTrue = False
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
.Item(.Count).StopIfTrue = False
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
.Item(.Count).StopIfTrue = False
End With

With .Range("A3:AA3000").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
.Item(.Count).StopIfTrue = False

End With
With .Range("A3:AA3000").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
.Item(.Count).StopIfTrue = False

End With
With .Range("A3:AA3000").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
.Item(.Count).StopIfTrue = False
End With
With .Range("A3:AA3000").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
.Item(.Count).StopIfTrue = False

End With
With .Range("A3:AA3000").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
.Item(.Count).StopIfTrue = False
End With
With .Range("A3:AA3000").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
.Item(.Count).StopIfTrue = False

End With
With .Range("A3:AA3000").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
.Item(.Count).StopIfTrue = False
End With

    Application.Calculation = xlAutomatic
    Application.CalculateBeforeSave = True
End With


End Sub
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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