VBA help - Deleting and replacing conditional formatting

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
681
Office Version
  1. 2019
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
What is in AA3?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
681
Office Version
  1. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
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.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
681
Office Version
  1. 2019
Platform
  1. Windows
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
 

willow1985

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

ADVERTISEMENT

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
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
681
Office Version
  1. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

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
Top