comparison of cell date + days

sampson32

Active Member
Joined
Jul 16, 2003
Messages
312
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Vinnie

How is it not working?

Why are you using code for this?

Could you not just use conditional formatting?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,134
Messages
6,106,141
Members
447,996
Latest member
ANDYADAM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top