Hi, first time posting here...I am trying to make a cell (A1) with a
date in it turn red within say 15 days, and then turn back to the
original color when the completion date in cell B1 is inserted.
Basically I'm making a training spreadsheet that will show training
coming due (with alerts...red color) and training complete dates. I
have pretty much figured out how to make A1 turn red within 15 days,
but I cannot figure out how to change it back to the original color
dependent on cell B1. Any help would be appreciated!!

Scott


--
tufftoy
------------------------------------------------------------------------
tufftoy's Profile: http://www.excelforum.com/member.php...o&userid=36567
View this thread: http://www.excelforum.com/showthread...hreadid=563137

One interp .. perhaps this might work ..

Select col A, click Format Conditional Formatting
then make the settings as:

Condition 1
=AND(A1<"",TODAY()-A1<=15,B1<"")
Format: "No format set"

Condition 2
=AND(A1<"",TODAY()-A1<=15)
Format: Red fill & white font, bolded

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tufftoy" wrote:
.. I am trying to make a cell (A1) with a
date in it turn red within say 15 days, and then turn back to the
original color when the completion date in cell B1 is inserted.
Basically I'm making a training spreadsheet that will show training
coming due (with alerts...red color) and training complete dates. I
have pretty much figured out how to make A1 turn red within 15 days,
but I cannot figure out how to change it back to the original color
dependent on cell B1. Any help would be appreciated!!

Try Conditional formatting.

But this forumla as the first condition

=IF($A1TODAY(),TRUE,FALSE) To see if date as happened

Format to original

then this one

=IF($A1-TODAY()-14<=15,TRUE,FALSE) to get your 15 day rule

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=563137

Some further clarifications ..

The earlier cond format formula:

Condition 1
=AND(A1<"",TODAY()-A1<=15,B1<"")

simply checks that col B isn't empty, re the part: B1<""
Any entries/inputs made within col B will hence trigger condition 1 (not
just dates). Under normal circumstances this would usually suffice

Perhaps a slightly stricter criteria, where we want the CF's condition 1 to
trigger only if a date is entered in col B (with col B presumed set to the
default General format), is to use instead an additional check in condition
1,

Condition 1, Formula is:
=AND(A1<"",TODAY()-A1<=15,B1<"",LEFT(CELL("format",B1),1)="D")
Format: "No format set"

Condition 2 (no change)
=AND(A1<"",TODAY()-A1<=15)
Format: Red fill & white font, bolded

Then only dates entered within col B will trigger condition 1 (as Excel
would "auto-format" any date inputs in date format)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

An implemented sample for the foregoing is available at:
http://cjoint.com/?hunUpF2yJD
Cond Format 15 day alert window w completion date.xls
(with screenshots of the CF settings)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---