Color code text from date range

[powertrick]

How do I color code 2 date text conditions. The text will be a date and if it's older than say 10 days then red etc in Excel 2003?

I could not get below to work:
Range("D8100").FormatConditions.Add(xlCellValue, xlGreater, CLng(Date - 10)).Interior.ColorIndex = 3

 

[Loomah]

>older than 10 days

from what?

also, why do you think you need vba for this - what is the context of your question?

this would give you the date 10 days back from now

dateserial(year(now()),month(now()),day(now()))- 10

for a starting point

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[powertrick]

If the cell has a datestamp of text in it that is older than 10 days then that datestamp text should color red with a timer routine that runs every few minutes.

I know there are other methods ie. macros but I have other code that I would like to keep it together with.

 

[powertrick]

Any thoughts on a solution to color date according to the amount of days it is old?

 

[Loomah]

hi, yeah
just had a couple of days off!

add this code to the workbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime dTime, "a", , False
End Sub

Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:10"), "a"
End Sub

and add this to a normal module

Option Explicit
Public dTime As Date
Sub a()
Dim c As Range
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "a"
For Each c In Range("A1:A10")
    If c < Now - 10 Then
        c.Font.Color = vbRed
    End If
Next

End Sub

this will run a procedure every 10 seconds from opening the file until closing it. it will chack the dates in the range a1:a10 and if they are 10 days or more old will change the font to red.

change your interval in Sub a() as appropriate and change your range containing dates as appropriate.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[powertrick]

Thanks Loomah it works great. My embedded skills is not really helping much with VBA so thanks a lot.

 

[powertrick]

Maybe something else you can help with please....I wanted to include an AND condition from the next column cell to color the text red. I cannot understand how to declare 2 ranges simultaneously.

 

[Loomah]

let me understand - you want to change the font of whichever cell and the one next to it?

assuming the answer to that is yes and by 'next column cell' you mean to the right then change

c.Font.Color = vbRed

to

Range(c, c.Offset(0, 1)).Font.Color = vbRed

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[powertrick]

Sorry not being clear....I have a column with dates, if the dates are older than 10 days and the adjacent column has an 'o' character in it then change the font of the date cell of that row to red.

 

[Loomah]

ok!

If c < Now - 10 [b]and c.offset(0,1)="o" [/b]then

OR
if the character can appear anywhere in a string in the adjacent cell then you could use

If c < Now - 10 [b]and instr(c.offset(0,1), "o") > 0[/b] then

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[powertrick]

Thanks that worked great