Problem with formulas

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
 
M

Max

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
 
V

VBA Noob

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
 
M

Max

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, 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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top