Problem with formulas

  • Thread starter Thread starter tufftoy
  • Start date Start date
T

tufftoy

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
 
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
 
Try Conditional formatting.

But this forumla as the first condition

=IF($A1>TODAY(),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 Noo
 
Some further clarifications ..

The earlier cond format formula:
Condition 1
=AND(A1<>"",TODAY()-A1<=15,B1<>"")
Click to expand...

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, re:

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)

---
 
Back
Top