Excel 2010 Conditional Formatting - Color based on different dates 1

[BlueHorizon]

Good day all!

I'm working with a date entered in cell A1. Here's what I need to have happen:

As soon as a date is entered in A1, B1 turns green to show its status.

At 9 months from the date in A1, I need B1 to turn yellow.

At 1 year plus 1 day in A1, I need B1 to turn red.

Do-able? Appreciate any help at all!


Best,
Blue Horizon

 

[Andrzejek]

That doesn't make any sense.
I enter in A1 a date of 5/5/2057, B1 turns green?
I enter in A1 a date of 1/1/1647, B1 turns green?

"At 9 months from the date in A1" 9 months before or after the date in A1

Have fun.

---- Andy

 

[BlueHorizon]

Here's my goal:

In A1, I enter 5/1/2012, cell B1 turns green.

In 9 months FROM the date entered in A1, when today's date is 2/1/2013, cell B1 turns yellow.

In 366 days, when today's date is 5/2/2013, cell B1 turns red.

Can you help?

Best,
Blue Horizon

 

[dhulbert]

I'm using 2007 but it shouldn't be much different on 2010

You can format a call based on a formula so if you use

=TODAY()-$C$4 < 30 as the formula then it will format the cell as Green for dates < 30 days

then add a second rule using

=TODAY()-$C$4 < 270

for your 9 months etc.

Works fine for me.


I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Andrzejek]

That should get you started:

Option Explicit
  
Private Sub Worksheet_Activate()
  
If Range("A1").Value <> "" Then
    If IsDate(Range("A1").Value) Then
        If (Date - Range("A1").Value) < 30 Then
            Range("B1").Interior.Color = vbGreen
        End If
    Else
        MsgBox "Cell A1 is not a Date"
    End If
Else
    MsgBox "Cell A1 is empty"
End If
  
End Sub

But I am sure dhulbert's approach will work, too.

Have fun.

---- Andy

 

[SkipVought]

Andy,

Why would you even THINK about using code for a CF?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BlueHorizon]

How would you do it, Skip, using CF?

Best,
Blue Horizon

 

[SkipVought]

as dhulbert posited, using multiple CF conditions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Skip, I wasn't thinking. Sorry. :-(

But... now BlueHorizon knows there is more than one way to 'skin the cat' (why anybody would want to 'skin the cat'...? )

Have fun.

---- Andy

 

[BlueHorizon]

Thank you all for taking the time to help! I'll try these and let you know....

Have a good week,
Best,
Kathy

Best,
Blue Horizon

 

[BlueHorizon]

A variation of dhulbert's solution worked great! A star for you!

Have a good week all,
Kathy

Best,
Blue Horizon