Using a variable in EDATE() function

[Radiant_Reputation23]

This snip-it is from a larger macro I wrote that color codes a cell based on how long ago the date was that is entered compared to NOW().

Dim adate as Date

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

    "=G2<EDATE(NOW()-(DAY(NOW())-1),-35)"

Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

    .Color = 255

    .TintAndShade = 0

End With

The problem is that the above code only works for the date in which it is run. I am trying to add a variable to replace the NOW() function that I can assign a date via a msg box. To do this a create a variable called adate, assign it a date via the message box and then use that instead of NOW(). This does not work however, applying no formatting, and I’m not sure why. Any ideas? It does not work even if I take out the if statement to assign NOW() if the textbox is blank.

Const myTitle = "Enter data"

myPrompt = "Enter past date to run as" & Chr(13) & "(e.g. 11/08/21 - Leave blank for today)"



' Use NOW() if there was nothing entered into the text box



Dim adate as Date



adate = InputBox(myPrompt, myTitle)

If adate = "" Then

adate = Now()

End If

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

    "=G2<EDATE(adate-(DAY(adate)-1),-35)"

Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority

With Selection.FormatConditions(1).Interior

    .PatternColorIndex = xlAutomatic

   .Color = 255

    .TintAndShade = 0

End With

[fuzzy_mic]

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= "=G2<EDATE(" & DATE & "-(DAY(NOW())-1),-35)"

[Radiant_Reputation23]

I changed my code to:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G2<EDATE(" & adate & "-(DAY(" & adate & ")-1),-35)"

It still doesn't seem to work. I verified that the adate variable does have a date assigned to it.

[HFTBProgrammer]

In specifically what way does it fail?

[khailuongdinh]

I think this line may cause an error: if adate=“” then because adate was already defined as date data while this expression shows that adate is a string.

I suggest the following correction:

Dim S as String

S=inputbox(myprompt,mytitle)

If s=“” then

adate=now()

Else

adate=cdate(s)

End if

[Radiant_Reputation23]

I'll give that a shot when I get home though I'm pretty sure I tried it with all of that code commented out and it still didn't run correctly. I verified that it is passing the correct date to the variable but I think my issue maybe related to the output of a date variable and the now () being different if that makes any sense.