Conditonal format Cells in Excel if older than 4 days

I can anyone help me with a slight niggly problem i am having in Excel Please?

i have the following code:

Sub DateMinus4()

Dim DateX
DateX4 = Date - 4

Range("E:E").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INT(E1)= DateX4()"
Selection.FormatConditions(1).Interior.ColorIndex = 4

MsgBox (DateX4)

End Sub



which basically is supposed to look at todays date. (Date) and take 4 days from it (Date-4) whiich is defined as a variable value of (DateX4) i can prve this bit works as the msgbox displays the correct date value, howevere what i want is for the code to look at a defined column in this case column E and if there is a date value in it of (DateX4) ie four days old, change the colour of the cell to Green, but i can get the syntax to work or the Date variabel to be recognised, can anyone help with the correct syntax to use a variabel in a statement such as this?

many thanks K.

Hi kkinsella and welcom to the forum. :)

How about you just use the TODAY() worksheet function in your formula?

Colin

Thanks Colin, I did try that initally but i couldnt work out how to get to take 4 days from today and use that date. any ideas would be great thanks.

Thanks Colin, I did try that initally but i couldnt work out how to get to take 4 days from today and use that date. any ideas would be great thanks.

Sure, how about:
TODAY()-4

Colin, many thanks, I actually got there but didnt realise it worked, forgot it actually counts 4 back from 21 to 17 but doesnt count the 17th, so i need to go back 3.

thanks again for speedy reply, just gettingto grips with VB and didnt want to fall at the first hurdle! :-)

Colin, Just a quick question, is there a way to do it with the Date option as i have another requirement that would possibly need this rather than the TODAY function?

You can actually squeeze it into a single line (not that that's necessarily a good thing):
Sheet.Range("E:E").FormatConditions.Add(xlCellValue, xlEqual, CLng(Date - 4)).Interior.ColorIndex = 4
The real advantage of Colin's suggestion over this one is that using TODAY() will just work, whereas you'll have to re-run the macro each day to update the formatting...

Thanks for replying, makes sense now, another thing i have noticed is that the entire column is highlighted in red is there a way to only highlight the cells with actual data in them? thanks for help.




Please post Excel questions, in the Excel forum.

Please use the .. tags when you post your code.

Thank you.

You can use
=NOT(ISBLANK(INDIRECT("RC",FALSE)))
as conditional formula. The Indirect() expression is a way to explicitly refer to "this cell", and Not(IsBlank()) tests whether there is data in the cell.

kkinsella, you can combine those tests in a conditional format like this:

=AND(ISNUMBER(Me), Me < TODAY() - 4)

... where Me refers to the cell in which it appears. I have this as a sheet-level name in my default template -- it's convenient for a lot of things.

To create it on, say, Sheet1, select cell A1 and do Insert > Name > Define, Sheet1!Me Refers to: =A1 (note no dollar signs).

I'm not sure about what relational operator you want ...

Thanks wil lgive that a try...

You can actually squeeze it into a single line (not that that's necessarily a good thing):
Sheet.Range("E:E").FormatConditions.Add(xlCellValue, xlEqual, CLng(Date - 4)).Interior.ColorIndex = 4
The real advantage of Colin's suggestion over this one is that using TODAY() will just work, whereas you'll have to re-run the macro each day to update the formatting...
HI when i try this code suggested it gives me an object not found error, can someone assist please?
Sheet.Range("E:E").FormatConditions.Add(xlCellValue, xlEqual, CLng(Date - 4)).Interior.ColorIndex = 4

You need to replace Sheet by the codename of the worksheet that you want to operate on, this will be Sheet1 etc. unless you changed it.