Change the color of a cell

  • Thread starter Thread starter Lou825
  • Start date Start date
L

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.
 
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))
 
If you have NOW() / Date-Time in B5, can use external cell A7 to have: 15
=MONTH(B5)+$A$7
 
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()))
 
guesse mine is not exactly what you asked, but an idea if flipping purposes /
values.. thanks
 
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.
 
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
 
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
 
Back
Top