Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with formulas
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with formulas
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!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with formulas
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with formulas
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with formulas
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Problem (progression in formulas) | Excel Worksheet Functions | |||
locking formulas?? | Excel Discussion (Misc queries) | |||
Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
Baffling formula problem | Excel Discussion (Misc queries) | |||
Tricky formulas needed | Excel Worksheet Functions |