I want 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 worked 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:
I need to modify my loop to 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.
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