Change the color of a cell

[Lou825]

I have Excel 2003 and I am having a problem with conditional formatting. I
have a sheet that I put dates in cells and need the cell to change color when
its 15 months from todays date.

 

[Gary Keramidas]

put a date in d1 and then create the conditional formatting formula and
criteria and see if it does what you want.

=(today())>DATE(YEAR(D1),MONTH(D1) +15,DAY(D1))

 

[MrDave]

If you have NOW() / Date-Time in B5, can use external cell A7 to have: 15
=MONTH(B5)+$A$7

 

[MiHee Jang]

as if b2 is first cell of dates, you open conditional formatting Dialogbox
and fill formulas as followed
=$B2=DATE(YEAR(TODAY())+1,MONTH(TODAY())+3,DAY(TODAY()))

 

[MrDave]

guesse mine is not exactly what you asked, but an idea if flipping purposes /
values.. thanks

 

[Lou825]

Thank you for helping me but I just cant get it to work. If I put 25AUG2010
in b2, thats with in 15 months from today I want the cell to change its
color. I petty much need to be notified when we are 15 months away from the
date I put in the cell.

 

[MrDave]

sorry, correction on macro, and:

to enter macro: right click on your sheet tab, bottom left, click on View
Code
paste item in window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row < 20 Then Exit Sub 'allows skip some rows
If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'allows skip some
rows

If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "B:B") 'Destination
.NumberFormat = "mm"
.Value = Now
End With
Application.EnableEvents = True
End With
End Sub

 

[MrDave]

you need to use a date that is recognized by excel. enter date as:
Cntrl ; (key stroke is: Control & semi-colon)
for time you add 2 spaces and enter: Cntrl Shift ;
which is the same as entering NOW(), =NOW() will not exactly work,

there is a macro for NOW() as follows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row < toprowID Then Exit Sub 'allows skip some rows
If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'allows skip some
rows


If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "B:B") 'Destination
.NumberFormat = "mm"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub