Change font color based on a validation check!

[Salmerin]

I have a simple worksheet which contains dates for specific events through out the years. Each event has an expiry date (2 yrs), so I would like to test D3 to Q16 (RangeName: "EventDates") to see if they have expired, and if so they will change Font Color to RED otherwise they will remain in BLUE.

ie: if an event was done back in 22-Jan-05, it's due date would be 22-Jan-07. So it means that based on today's date, it has expired already, so instead of having a blue font, it will then change to Red.

Can anyone help out with this one?

[FrankBoston]

Look at the attached workbook

The conditional format formula you could use is:
=DATE(YEAR(A4)+2,MONTH(A4),DAY(A4))<NOW()

Select a Pattern as the color you want

[Salmerin]

Look at the attached workbook

The conditional format formula you could use is:
=DATE(YEAR(A4)+2,MONTH(A4),DAY(A4))<NOW()

Select a Pattern as the color you want

Can this be done using VBA? I am using Excel 2007 and would like this using Conditional formatting but with VBA.

Is this possible in 2007?


The range I want this to check upon entry (ie: OnEntry), is from Range("D3:Q16"), if a cell contains nothing then nothing will be changed, if a cell has a date then check to see if 2 yrs have passed since that day, if so then change its color to RED thereby letting me know that that event has expired.

??

[FrankBoston]

The VBA solution would be to have a sub search the range of dates, check each one for expiration, and set the font color to blue or red. You probably wouldn't use Conditional Formatting.

I suppose you could use a sub to assign the conditional format to the rectangle of date cells.

Or, you can Edit: Copy: Paste - Formats (includes conditional formats) to the range holding the dates.

Why is your preference for VBA in this case?

[Salmerin]

The VBA solution would be to have a sub search the range of dates, check each one for expiration, and set the font color to blue or red. You probably wouldn't use Conditional Formatting.

I suppose you could use a sub to assign the conditional format to the rectangle of date cells.

Or, you can Edit: Copy: Paste - Formats (includes conditional formats) to the range holding the dates.

Why is your preference for VBA in this case?

Mainly because I am trying to gear towards VBA and the more I learn the better, I am trying to do everything using VBA....just to have a programming experience I suppose.

[potterbuff]

I have a simple worksheet which contains dates for specific events through out the years. Each event has an expiry date (2 yrs), so I would like to test D3 to Q16 (RangeName: "EventDates") to see if they have expired, and if so they will change Font Color to RED otherwise they will remain in BLUE.

ie: if an event was done back in 22-Jan-05, it's due date would be 22-Jan-07. So it means that based on today's date, it has expired already, so instead of having a blue font, it will then change to Red.

Can anyone help out with this one?

Hi,

I'm looking for something along the same lines, only I want something that changes colour with dates as it approaches deadline (in blue) and past deadline (in bold red). It's for a task list for work. I used to work with something like this but I didn't set it up so not a clue where to start. Can anyone help?

[FrankBoston]

' Find each cell in named range, check date
' Apply font color according to age
'
Sub ColorDates()
  Dim j As Long, twoYrs As Date
  'two years ago
  twoYrs = DateSerial(Year(Date) - 2, Month(Date), Day(Date))

For Each ss In ActiveSheet.Range("EventDates")
  If Not IsDate(ss) Then GoTo skip
  If ss > twoYrs Then
    ss.Font.ColorIndex = 5 'set font to Blue
  Else
    ss.Font.ColorIndex = 3 'set font to Red
  End If
skip:
  Next ss
End Sub

[Salmerin]

' Find each cell in named range, check date
' Apply font color according to age
'
Sub ColorDates()
  Dim j As Long, twoYrs As Date
  'two years ago
  twoYrs = DateSerial(Year(Date) - 2, Month(Date), Day(Date))

For Each ss In ActiveSheet.Range("EventDates")
  If Not IsDate(ss) Then GoTo skip
  If ss > twoYrs Then
    ss.Font.ColorIndex = 5 'set font to Blue
  Else
    ss.Font.ColorIndex = 3 'set font to Red
  End If
skip:
  Next ss
End Sub

Thanks, this works for me!