For loop to fill Interior.ColorIndex unless cell is blank

Question

I'm making a macro command button to color in the cells of a range based on whether the projected date (col"X") is before, equal to, or after the actual date (col"Y"). It's working perfectly fine until I tried to introduce the default, which is NO interior color and the text "not complete".

Sub ColorChangeTATDates()

Dim lr As Long
lr = Sheet1.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Dim i As Long, projected As Range, actual As Range
For i = 3 To lr
  Set projected = Range("X" & i)
  Set actual = Range("Y" & i)
  If actual.Value = " " Then actual.Interior.ColorIndex = 0 And actual.Cells.Value = "not complete"
  If actual.Value < projected.Value Then actual.Interior.ColorIndex = 8
  If actual.Value >= projected.Value Then actual.Interior.ColorIndex = 4
  Debug.Print (projected.Value)
  Debug.Print (actual.Value)
Next i

End Sub

What happens: All empty cells turn blue. "not complete" does not print to empty cells.

I need to modify my loop to basically set the "Y" range cell value to "not complete" and no fill (which I feel like I shouldn't have to specify) until certain criteria is met. But I'm still a baby coder so I am having trouble formatting it without it getting more complex than it needs to be.

Answer 1

This line:

 If actual.Value = " " Then actual.Interior.ColorIndex = 0 And actual.Cells.Value = "not complete"

Is actually read like:

If actual.Value = " " Then actual.Interior.ColorIndex = (0 And actual.Cells.Value = "not complete")

Which rectifies to something like

If actual.Value = " " Then actual.Interior.ColorIndex = (FALSE And FALSE)

The reason this is happening is because And compares two conditions, so it treats both 0 and actual.Cells.Value = "not complete" as a condition. 0 is exactly equal to the boolean value False and actual.Cells.Value = "not complete" at this point in the execution will also be False. So really you are just setting the interior.colorindex property to False And False which is False which is just 0.

At any rate, you want to run two lines of code when that if condition is true, so you'll need to use an if block instead of a one-liner:

If actual.Value = " " Then 
   actual.Interior.ColorIndex = 0 
   actual.Cells.Value = "not complete"
End If

You may also want to check on that If actual.Value = " " Then line since " " is a literal space character. An empty cell would be ""

Full Code:

Sub ColorChangeTATDates()

    Dim lr As Long
    lr = Sheet1.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    
    Dim i As Long, projected As Range, actual As Range
    
    For i = 3 To lr
        Set projected = Range("X" & i)
        Set actual = Range("Y" & i)
        
        If actual.Value = " " Then
            actual.Interior.ColorIndex = 0
            actual.Cells.Value = "not complete"
        End If
        
        If actual.Value < projected.Value Then actual.Interior.ColorIndex = 8
        If actual.Value >= projected.Value Then actual.Interior.ColorIndex = 4
        
        Debug.Print (projected.Value)
        Debug.Print (actual.Value)
    Next i
End Sub