title: Use a date value in Excel to apply conditional formatting with VBA
body: <p>I am creating an Excel sheet that will show when customers have paid and when they are late. With the help of a friend, I was able to come up with a formula that will tell us if a customer is past their due date by highlighting the cell red.</p>
  
  <p>As far as I've seen, there's no way to get conditional formatting to change the blank cell to say "LATE", so I was suggested to try out VBA.</p>
  
  <p>I have been able to change the colors and add the words I need, but how to get VBA to check the dates like the conditional formatting formula?</p>
  
  <p>Here is the formula and what I have in my VBA sheet:</p>
  
  <p><strong>Formula</strong></p>
  
  <pre><code>=AND(ISBLANK(B1),NOT(ISBLANK(B$1)),NOT(ISBLANK($A1)),DATE(YEAR($A1),MONTH($A1),DAY(B$2))&lt;TODAY(),B$2&lt;TODAY())
  </code></pre>
  
  <p><strong>VBA</strong></p>
  
  <pre><code>Const FORMATTED_RANGE As String = "$B:$ZY"
  
  Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range(FORMATTED_RANGE)) Is Nothing Then Exit Sub
    Dim cellColor As Long
    Application.EnableEvents = False
    Select Case Len(Target.Value)
    Case ISBLANK
        Target.Value = "LATE"
        Target.Interior.Color = vbRed
    Case Is &lt;&gt; 0
        Target.Value = "PAID"
        Target.Interior.ColorIndex = 10
    End Select
    Application.EnableEvents = True
  End Sub
  </code></pre>
  
  <p>I used some code I found online (thank you Rolf Jaeger from nullskull.com) for this so I'm not 100% if I need everything there, but it seems to be working fine, so I left it for now until I learn more about VBA.</p>