VBA formatting based on dates

[ffionnah]

Hi,
Thank you, in advance, for any contributions to this code.

The code below isn't formatting. Essentially, what I need this code to do is start in column "D", row 3, and search for dates, highlighting any date that is within -4 to +2 days of a date listed in A1. I want this to then jump to column "G" and do the same thing, continuing this process for every 3 columns, until it reaches the last column, "Y". The goal is to have any date -4 to -1 be yellow, 0 would be green and +1 and +2 blue. The cells in column "A" would also be either green or blue, depending on the date.



Sub Datecolor()
Dim i As Long, j As Long, myDate As Date

myDate = Cells(1, 1)

For j = 4 To 25 Step 3

For i = 3 To 10000
If Cells(i, j) = myDate - 4 Then Cells(i & j).Interior.ColorIndex = 36 And Cells(i, 1).Interior.ColorIndex = 35
If Cells(i, j) = myDate - 3 Then Cells(i & j).Interior.ColorIndex = 36 And Cells(i, 1).Interior.ColorIndex = 35
If Cells(i, j) = myDate - 2 Then Cells(i & j).Interior.ColorIndex = 36 And Cells(i, 1).Interior.ColorIndex = 35
If Cells(i, j) = myDate - 1 Then Cells(i & j).Interior.ColorIndex = 36 And Cells(i, 1).Interior.ColorIndex = 35
If Cells(i, j) = myDate + 0 Then Cells(i & j).Interior.ColorIndex = 35 And Cells(i, 1).Interior.ColorIndex = 35
If Cells(i, j) = myDate + 1 Then Cells(i & j).Interior.ColorIndex = 37 And Cells(i, 1).Interior.ColorIndex = 37
If Cells(i, j) = myDate + 2 Then Cells(i & j).Interior.ColorIndex = 37 And Cells(i, 1).Interior.ColorIndex = 37
Next i
Next j

End Sub

 

[BobUmlas]

Change "And" to ":" The way you have it, the And will give True or False so your statement is If(Cells(i,j)=Mydate then TRUE (or false)

 

[ffionnah]

Thank you for the input. That did correct formatting for Column "A", however, the columns that house the dates are still not being formatted. These columns are D,G,J,M,P,S,V,Y.

 

[Joe4]

All your Cells(i & j) references need to be changed to Cells(i, j)
Oddly, you did that correctly in the IF part of your code, but not in the THEN part of it.

 

[ffionnah]

Ahhh, thank you; totally overlooked that!

 

[Joe4]

You are welcome.

And welcome to the board!