title: Excel VBA Multiple Conditional Formatting
body: <p>I'm new to VBA. I know this isn't the best way to do what I'm doing, so any help on ideas for improvement and general methodology explanation would be greatly appreciated!</p>
  
  <p>So I have three different formats I want. The lesser than, the greater than, and a blank cell format.</p>
  
  <p>Pretty much my code runs across a row checking each value against a value at the very end of that row (goal). If it's greater or equal to the goal, it turns green. If it's lesser, red. If the goal cell is empty, all cells are blank (formatting is white background, black text). If a cell is empty, the cell is blank (white background).</p>
  
  <p>I ran all 5 macros in the order, Lesser, Greater, Equal, Empty Goal, Empty Cell. This created a nice looking Excel with correct format.</p>
  
  <p>When you change the goal number, the cells in that row change respectively. Great! However, when you add a new number to a previously empty goal, or delete a goal number, you have to run the macros all over again to get the formatting.</p>
  
  <p>Side note: For my code, I recorded myself doing one row of what I wanted (except for Empty Cell), then just turned it into a loop down the additional rows.</p>
  
  <p>Any idea on how to eliminate re-running macros? Ideally this Excel document will get sent to someone who doesn't know anything about VBA.</p>
  
  <pre><code>Sub Greater()
  Dim i As Integer
  Dim myRange As String
  Dim myComparison As String
  For i = 4 To 97
      myRange = "C" &amp; i &amp; ":BC" &amp; i
      myComparison = "=$BD$" &amp; i
      Range(myRange).Select
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
          Formula1:=myComparison
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
          .Color = -16752384
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .Color = 13561798
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      Next i
  End Sub
  </code></pre>
  
  <p>x</p>
  
  <pre><code>Sub Lesser()
  Dim i As Integer
  Dim myRange As String
  Dim myComparison As String
  For i = 4 To 97
      myRange = "C" &amp; i &amp; ":BC" &amp; i
      myComparison = "=$BD$" &amp; i
      Range(myRange).Select
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
          Formula1:=myComparison
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
          .Color = -16383844
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .Color = 13551615
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      Next i
  End Sub
  </code></pre>
  
  <p>x</p>
  
  <pre><code>Sub Equal()
  Dim i As Integer
  Dim myRange As String
  Dim myComparison As String
  For i = 4 To 97
      myRange = "C" &amp; i &amp; ":BC" &amp; i
      myComparison = "=$BD$" &amp; i
      Range(myRange).Select
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
          Formula1:=myComparison
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
          .Color = -16752384
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .Color = 13561798
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      Next i
  End Sub
  </code></pre>
  
  <p>x</p>
  
  <pre><code>Sub EmptyGoal()
  Dim i As Integer
  Dim myRange As String
  Dim myComparison As String
  For i = 4 To 97
      myRange = "C" &amp; i &amp; ":BC" &amp; i
      Range(myRange).Select
      Selection.FormatConditions.Add Type:=xlExpression, Formula1:=IsEmpty(Cells(i, 56))
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Font
          .ThemeColor = xlThemeColorLight1
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
      Next i
  End Sub
  </code></pre>
  
  <p>x</p>
  
  <pre><code>Sub EmptyCell()
      Range("C4:BC97").Select
      Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=LEN(TRIM(C4))=0"
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
  End Sub
  </code></pre>