willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 888
- Office Version
- 365
- Platform
- 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
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