comparison of cell date + days

[sampson32]

The code below compares the cells in “H” to the cells in “G” and colors the “H” cells if the 2 dates (values) are equal.
I can successfully get it to work with = but how do I alter this code to only color “H” cells when the dates (values) in “H” are 2 days greater than the dates(values) in “G” cells?

I tried several variations:

If Range("H" & i).Value = Range("G" & i).Value + 2 Then

If Range("H" & i).Value >= Range("G" & i).Value + 2 Then

But they won’t work

Any help would be gratefully appreciated

Thank you,

Vinnie


*********************************


Private Sub Workbook_Open()
Dim i As Long, myLastRow As Long

For i = 3 To myLastRow

If Range("H" & i).Value <> "" Then
If Range("H" & i).Value = Range("G" & i).Value Then
Range("H" & i).Interior.ColorIndex = 5
End If
End If
Next i

End Sub

 

[Norie]

Vinnie

How is it not working?

Why are you using code for this?

Could you not just use conditional formatting?

 

[sampson32]

Hi –

I could use conditional formatting but I like VBA and that’s the best way for me to learn.

What’s not working?

This works…
***********************

Private Sub Workbook_Open()
Dim i As Long, myLastRow As Long

For i = 3 To myLastRow

If Range("H" & i).Value <> "" Then
If Range("H" & i).Value = Range("G" & i).Value Then
Range("H" & i).Interior.ColorIndex = 5
End If
End If
Next i

End Sub
*************************

This code colors (Blue) the cells in H column that have matching values in G column.

***This was written as a test only – just to get the code to function as a starting point!

I actually want the H cells colored (red) only if their dates (values) are 2 or more days greater than the dates (values) in G

G has 10-29-05
H has 10-30-05 = H not colored

G has 10-29-05
H has 10-31-05 = H colored (H has a date (value) that is at least 2 days greater than G)



As I said I tried variations of this line; If Range("H" & i).Value = Range("G" & i).Value Then

But I don’t know how to properly amend it.

Thank you,

Vinnie

 

[Norie]

Vinnie

You still haven't stated clearly how the code isn't working.

Have you tried stepping through it using F8?

You should then be able to see if the if statement is working as expected.

You might also want to look at the DateDiff function to work out the number of days between the 2 dates.

 

[sampson32]

The code below colors any cells in column “H” that contain a date equal to or greater than the date in column G’s cells on a row by row basis.

And this works.
**************************

Private Sub Workbook_Open()
Dim i As Long, myLastRow As Long
myLastRow = Range("D65536").End(xlUp).Row
For i = 3 To myLastRow

If Range("H" & i).Value <> "" Then
If Range("H" & i).Value >= Range("G" & i).Value Then

Range("H" & i).Interior.ColorIndex = 3
End If
End If
Next i

End Sub

***************************
I am trying to change the above code to color only cells in column “H” that contains a date 2 days greater than dates in column G cells

I need to pragmatically add two days to this:” Range ("G" & i).Value” so that “H” “trues”only on dates that are >= to “G” values plus two days.


I tried adding + 2 as in

Private Sub Workbook_Open()
Dim i As Long, myLastRow As Long
myLastRow = Range("D65536").End(xlUp).Row
For i = 3 To myLastRow

If Range("H" & i).Value <> "" Then
If Range("H" & i).Value >= Range("G" & i).Value + 2 Then

Range("H" & i).Interior.ColorIndex = 3
End If
End If
Next i

End Sub
But I get a Run-time error 13 / Type mismatch with this line of code highlighted
“If Range("H" & i).Value >= Range("G" & i).Value + 2 Then”

Any Ideas?

I don’t know what else to tell you.

Vinnie