Change color of cell when dates match (4 conditions)

[ceslyn]

Anyone can tell me how to make this work?
I'm always getting the amount of 40611 and my cells are not changing.

Any help would be appreciated.

Thanks!


Sub findate()
Dim strDate1, strDate2 As Date
strDate1 = myplage
strDate2 = Now
testdates = DateDiff("d", strDate1, strDate2)
msgbox (testdates)
Set myplage = Range("B6:N18")
For Each cell In myplage
testdates = DateDiff("d", strDate1, strDate2)
If testdates > 5 Then Range("b6").Interior.Color = RGB(200, 160, 35)
If testdates < 5 Then Range("b6").Interior.Color = RGB(255, 255, 255)
If testdates = 5 Then Range("b6").Interior.Color = RGB(50, 50, 50)
Next
End Sub

 

[Andrew Poulsom]

Where have you assigned a value to myplage? What you are getting is the serial number for today's date.

 

[ceslyn]

myplage is actually the range of cells that it's going thru.

I've managed to make it work but as soon as enter the loop, i'm always getting the same number.

 

[Andrew Poulsom]

Try (untested):

Sub findate()
    Dim strDate1, strDate2 As Date
    strDate2 = Now
    Set myplage = Range("B6:N18")
    For Each cell In myplage
        strDate1 = cell.Value
        testdates = DateDiff("d", strDate1, strDate2)
        If testdates > 5 Then Range("b6").Interior.Color = RGB(200, 160, 35)
        If testdates < 5 Then Range("b6").Interior.Color = RGB(255, 255, 255)
        If testdates = 5 Then Range("b6").Interior.Color = RGB(50, 50, 50)
    Next
End Sub

 

[ceslyn]

I'm getting a type mismatch.

 

[Andrew Poulsom]

Do all the cells in B6:N18 contain serial dates?

 

[Darren Bartrup]

Might be completely wrong - anything to do with strDate1 being a Variant, and strDate2 being a date?

 

[ceslyn]

No all cells contain a date.
I've tried putting dates everywhere but it's only changing the color of the first cell.

 

[ceslyn]

Can anyone explain to me why I'm getting a value of 40611.
Here is another code i'm trying.

Sub CellsExample()
Dim Startdate As Date
Dim Enddate As Date

strDate2 = Now
Enddate = Now

For i = 6 To 8
For j = 2 To 4
If Cells(i, j).Value <> "" Then
Enddate = Cells(i, j).Value

testdates = DateDiff("d", Startdate, Enddate)
If testdates > 39881 Then Cells(i, j).Interior.Color = RGB(200, 160, 35)
If testdates > 40661 Then Cells(i, j).Interior.Color = RGB(50, 50, 50)
If testdates < 398815 Then Cells(i, j).Interior.Color = RGB(255, 255, 255)
msgbox (testdates)
Else
End If

Next j
Next i
End Sub

 

[Andrew Poulsom]

You haven't assigned a value to Startdate.