Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
For Red:
Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will notchange
What does A1 actually contain?
Pete On Feb 4, 5:03*pm, Mark wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 *cell DOES turn red. If I change the second date(01/21/2009) (20days) *to 1/04/2009 ( 3 days) the cell Stays RED * or if I have a green cell IT STAYS GREEN when the dates are altered.. *Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding * Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
Try this
Condition1: (Red) =AND(ISNUMBER(A1),DAY(A1)<14) Condition2: (Green) =AND(ISNUMBER(A1),dar(A1)<15) Milo "Mark" wrote in message ... For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
Sorry!
Condition1: (Red) =AND(ISNUMBER(A1),DAY(A1)<14) Condition2: (Green) =AND(ISNUMBER(A1),dar(A1)<15) <------ sorry mistyped =AND(ISNUMBER(A1),DAY(A1)<15) <------ this one correct Milo "milo" wrote in message ... Try this Condition1: (Red) =AND(ISNUMBER(A1),DAY(A1)<14) Condition2: (Green) =AND(ISNUMBER(A1),dar(A1)<15) Milo "Mark" wrote in message ... For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
Sorry again!
I hope this should help Condition1: (For Red) =AND(ISNUMBER(A1),DAY(A1)<=14) Condition2: (For Green) =AND(ISNUMBER(A1),DAY(A1)=15) Regards, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
A1 contains a date 01/01/2009
A2 contains a date 01/22/2009 A3 would be the difference in that date (turns red) I set conditonal in A3 if equal to or less than 14 turn green Add....If greather than 14 turn red. Whichever color comes up 1st in A3 be it a red number or green, the other conditon does NOT change if I change the date in A2 to )1/03/2009 or (2) it should turn green but it stays red.. It stays at the 1st color associated with the 1st date entered in A2 . Even if I F9 or shift F9 with a new date in A2 the color will not change from whatever came in 1st. Thanks A1 = 01/01/2009 A2 = 01/10/2009 "Mark" wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will notchange
So why did you quote A1 in the formula that you gave in your first
post? I suggest you check that those formulae actually refer to A3. Anyway, you have an overlap on those conditions. The first one (which takes priority) is looking for values greater than or equal to 14, whereas the second one is less than 15, so the first criteria will always apply if the number is 14. Hope this helps. Pete On Feb 4, 7:10*pm, Mark wrote: A1 contains a date 01/01/2009 A2 contains a date 01/22/2009 * A3 would be the difference in that date (turns red) I set conditonal in A3 if equal to or less than 14 turn green Add....If *greather than 14 turn red. Whichever color comes up 1st in A3 be it a red number or green, *the other conditon does NOT change if I change the date in A2 to )1/03/2009 or (2) it should turn green but it stays red.. It stays at the 1st color associated with the 1st date entered in A2 . Even if I F9 or shift F9 with a new date in A2 the color will not change from whatever came in 1st. Thanks A1 = 01/01/2009 A2 = 01/10/2009 "Mark" wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 *cell DOES turn red. If I change the second date(01/21/2009) (20days) *to 1/04/2009 ( 3 days) the cell Stays RED * or if I have a green cell IT STAYS GREEN when the dates are altered.. *Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding * Mark- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not ch
Came up with:
you may not use unions, intersections or array constants for conditional formatting criteria "milo" wrote: Try this Condition1: (Red) =AND(ISNUMBER(A1),DAY(A1)<14) Condition2: (Green) =AND(ISNUMBER(A1),dar(A1)<15) Milo "Mark" wrote in message ... For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not ch
I'm sorry that I didn't write A3, but I see ,you see the problem and I hear
you, that one overides the other. But if I change the date in A2 and it changes A3 to</14 Is there no conditional formula that will work to make it red/green based on </14? thanks pete very much "Pete_UK" wrote: So why did you quote A1 in the formula that you gave in your first post? I suggest you check that those formulae actually refer to A3. Anyway, you have an overlap on those conditions. The first one (which takes priority) is looking for values greater than or equal to 14, whereas the second one is less than 15, so the first criteria will always apply if the number is 14. Hope this helps. Pete On Feb 4, 7:10 pm, Mark wrote: A1 contains a date 01/01/2009 A2 contains a date 01/22/2009 A3 would be the difference in that date (turns red) I set conditonal in A3 if equal to or less than 14 turn green Add....If greather than 14 turn red. Whichever color comes up 1st in A3 be it a red number or green, the other conditon does NOT change if I change the date in A2 to )1/03/2009 or (2) it should turn green but it stays red.. It stays at the 1st color associated with the 1st date entered in A2 . Even if I F9 or shift F9 with a new date in A2 the color will not change from whatever came in 1st. Thanks A1 = 01/01/2009 A2 = 01/10/2009 "Mark" wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
I see what one of the issues are.
If I make a cell C7 and it is =C7-C6 and use just numbers not dates , they will turn green or red based on 2 conditionals of equal to or less than 14 shade green add Greater than 14 shade red. THAT works! But when I use DATES! The sum in my total box C7 does not compute to a real number. It is looking at it as something else other than the number. Maybe date days or something . so I guess the answer is to make that difference in dates a number or some kind of special value? Any ideas? Mark "Mark" wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
And to reclarify,
A1 you spoke of is actually C7 which is the difference in days between dates entered in C5 and C6 I knew to change the cell number I was using a generic, but nonethe less once you turn a cell a color it STAYS THAT WAY REGARDLESS OF ANY DATE CHANGE ABOVE. Yopu can't even get it to go back whit. If it's red it's always red.. Seems like a flaw. Mark Confused yet.. "Mark" wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will notchange
I can't get back to you at the moment, Mark - football on TV !!
Pete On Feb 4, 9:08*pm, Mark wrote: And to reclarify, A1 you spoke of is actually C7 which is the difference in days between dates entered in C5 and C6 I knew to change the cell number I was using a generic, but nonethe less once you turn a cell a color it STAYS THAT WAY REGARDLESS OF ANY DATE CHANGE ABOVE. Yopu can't even get it to go back whit. If it's red it's always red.. * Seems like a flaw. Mark Confused yet.. "Mark" wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 *cell DOES turn red. If I change the second date(01/21/2009) (20days) *to 1/04/2009 ( 3 days) the cell Stays RED * or if I have a green cell IT STAYS GREEN when the dates are altered.. *Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding * Mark- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional format color is set correct, yet color will not change
Hi,
Seems that you want the conditional occur only on cell A3. What I can see is:- A1 contains a date 01/01/2009 A2 contains a date 01/22/2009 A3 contains the different of how many days when you subtract date A1 from A2 " =A2-A1". And this cell must come with conditional format if the day equal to less then or equal to 14 should turns red and if it is greater than or equal to 15 should turns green. Suggestions: 1) Firstly, Try the following conditional formats and apply them to cell A3: Conditions 1: (Red) =AND(ISNUMBER(A3),DAY(A3)<=14) Conditions 2: (Green) =AND(ISNUMBER(A3),DAY(A3)=15) 2) Secondly, make sure font format in cell A3 not formated as "Red" or "Green" in color. 3) Lastly, make sure you have entered a valid date in cell A1 and A2. (d/m/yyyy or m/d/yyyy) 4) Ok, finally, probably you should try any of these formula instead and put it in cell A3. =ABS(A1-A2) or =Datedif(A1,A2,"md") Regards. "Mark" wrote in message ... A1 contains a date 01/01/2009 A2 contains a date 01/22/2009 A3 would be the difference in that date (turns red) I set conditonal in A3 if equal to or less than 14 turn green Add....If greather than 14 turn red. Whichever color comes up 1st in A3 be it a red number or green, the other conditon does NOT change if I change the date in A2 to )1/03/2009 or (2) it should turn green but it stays red.. It stays at the 1st color associated with the 1st date entered in A2 . Even if I F9 or shift F9 with a new date in A2 the color will not change from whatever came in 1st. Thanks A1 = 01/01/2009 A2 = 01/10/2009 "Mark" wrote: For Red: Condition1: Formula Is =AND(ISNUMBER(A1),A1=14) For Green: Condition2 Formula Is =AND(ISNUMBER(A1),A1<15) If I put in 01/01/2009 - 01/21/2009 cell DOES turn red. If I change the second date(01/21/2009) (20days) to 1/04/2009 ( 3 days) the cell Stays RED or if I have a green cell IT STAYS GREEN when the dates are altered.. Is this a one time shot?.. Any ideas And I apologise if I have posted this more than once . I am over my head and have reached out on 3 topics... Thanks for understanding Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox to change background color, font color and remove/ add bo | Excel Discussion (Misc queries) | |||
Conditional format: change row color based on cell value | Excel Discussion (Misc queries) | |||
change color of entire row by use conditional format | Excel Worksheet Functions | |||
How do you use conditional format to change a bar color on a char. | Charts and Charting in Excel | |||
Conditional Format: Expired dates wanting cell to change color | Excel Discussion (Misc queries) |