title: Excel VBA- How do I create conditional formatting without duplicates?
body: <p>Basically I'm trying to make conditional formatting that applies to all sheets in my workbook, since I'm not aware of any way to do this I figured I'd make a VBA script that creates it for each worksheet. It works but will create duplicates if they already had the formatting, is there any way to prevent this? See the image below has multiple copies of the same conditional formatting because I ran the VBA script multiple times, I want to check if conditional formatting with a specific formula exists to prevent this but haven't had any luck finding out how to do this.</p>
  
  <p>Duplicate Conditional Formats:
  <a href="https://i.stack.imgur.com/avtzL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/avtzL.png" alt="enter image description here"></a></p>
  
  <pre><code>Sub Macro1()
  
  Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets   
      If ws.Name = "macrotest" Then
          ws.Activate
  
          Dim myRange As Range
              Set myRange = Range("A1:GJH5000")
  
          Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
              "=AND(Sheet2!$A$1=TRUE, OR(CELL(""col"") - 5 &gt; CELL(""col"",A1), CELL(""col"") + 1 
                &lt;CELL(""col"",A1), CELL(""row"") - 1 &gt; CELL(""row"",A1), CELL(""row"") + 3 
                &lt;CELL(""row"",A1)))"
  
          Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  
          With Selection.FormatConditions(1).Interior
              .PatternColorIndex = xlAutomatic
              .ThemeColor = xlThemeColorLight1
              .TintAndShade = 0
          End With
  
          Selection.FormatConditions(1).StopIfTrue = False
          With Selection.FormatConditions(1).ModifyAppliesToRange(myRange)
          End With
      End If
  Next ws
  End Sub
  </code></pre>
  
  <p>I could just delete any existing conditional formatting using formatconditions.delete but I don't want to delete other conditional formatting. any ideas?</p>