|
Two Separate Conditions for Cond. formatting
1st need: Shading every other row. I have this figured out.
2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue) |
Two Separate Conditions for Cond. formatting
All that's necessary is to simply enter your date formula as the *first*
condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message oups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue) |
Two Separate Conditions for Cond. formatting
On Jun 26, 6:02 pm, "Ragdyer" wrote:
All that's necessary is to simply enter your date formula as the *first* condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message oups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue)- Hide quoted text - - Show quoted text - Unless I am doing something wrong it isn't working. The shaded rows are picking up the conditioning on the dates I think. |
Two Separate Conditions for Cond. formatting
That was the request!
The date format should supercede the row format. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "VegasSageV" wrote in message oups.com... On Jun 26, 6:02 pm, "Ragdyer" wrote: All that's necessary is to simply enter your date formula as the *first* condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message oups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue)- Hide quoted text - - Show quoted text - Unless I am doing something wrong it isn't working. The shaded rows are picking up the conditioning on the dates I think. |
Two Separate Conditions for Cond. formatting
On Jun 26, 7:12 pm, "Ragdyer" wrote:
That was the request! The date format should supercede the row format. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"VegasSageV" wrote in message oups.com... On Jun 26, 6:02 pm, "Ragdyer" wrote: All that's necessary is to simply enter your date formula as the *first* condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message groups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue)- Hide quoted text - - Show quoted text - Unless I am doing something wrong it isn't working. The shaded rows are picking up the conditioning on the dates I think.- Hide quoted text - - Show quoted text - I'm sorry, I did not explain it properly. I am using the following: CF1--Cell Value is, less than, =TODAY()-320" turns date Red. CF2--Formula is, =MOD(ROW(),2)=1 turns every other row shaded. What happens with the above scenario is that for every cell that should be shaded blue (CF2), if it also has the red date (CF1), it does not shade as it should for CF2. Instead it is the default white, no border. Hopefully this explains it better. I am new to the group and somewhat new to excel. Thanks. |
Two Separate Conditions for Cond. formatting
Let's start at the top.
Answer these questions.. Exactly what rows and columns are you including in the CF? Is it something like A1 to Z100 ... or what? Exactly what rows and/or columns will contain a date? Is it something like A1 to A100 ... or A1 to Z1 ... or *every cell ... or what? Are the dates keyed in, or are they the returns from formulas, or are they imported from another application? With this info I'll try to duplicate your scenario as closely as possible. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "VegasSageV" wrote in message oups.com... On Jun 26, 7:12 pm, "Ragdyer" wrote: That was the request! The date format should supercede the row format. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"VegasSageV" wrote in message oups.com... On Jun 26, 6:02 pm, "Ragdyer" wrote: All that's necessary is to simply enter your date formula as the *first* condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message groups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue)- Hide quoted text - - Show quoted text - Unless I am doing something wrong it isn't working. The shaded rows are picking up the conditioning on the dates I think.- Hide quoted text - - Show quoted text - I'm sorry, I did not explain it properly. I am using the following: CF1--Cell Value is, less than, =TODAY()-320" turns date Red. CF2--Formula is, =MOD(ROW(),2)=1 turns every other row shaded. What happens with the above scenario is that for every cell that should be shaded blue (CF2), if it also has the red date (CF1), it does not shade as it should for CF2. Instead it is the default white, no border. Hopefully this explains it better. I am new to the group and somewhat new to excel. Thanks. |
Two Separate Conditions for Cond. formatting
On Jun 26, 7:51 pm, "Ragdyer" wrote:
Let's start at the top. Answer these questions.. Exactly what rows and columns are you including in the CF? Is it something like A1 to Z100 ... or what? Exactly what rows and/or columns will contain a date? Is it something like A1 to A100 ... or A1 to Z1 ... or *every cell ... or what? Are the dates keyed in, or are they the returns from formulas, or are they imported from another application? With this info I'll try to duplicate your scenario as closely as possible. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"VegasSageV" wrote in message oups.com... On Jun 26, 7:12 pm, "Ragdyer" wrote: That was the request! The date format should supercede the row format. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------*"VegasSageV" wrote in message roups.com... On Jun 26, 6:02 pm, "Ragdyer" wrote: All that's necessary is to simply enter your date formula as the *first* condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message groups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue)- Hide quoted text - - Show quoted text - Unless I am doing something wrong it isn't working. The shaded rows are picking up the conditioning on the dates I think.- Hide quoted text - - Show quoted text - I'm sorry, I did not explain it properly. I am using the following: CF1--Cell Value is, less than, =TODAY()-320" turns date Red. CF2--Formula is, =MOD(ROW(),2)=1 turns every other row shaded. What happens with the above scenario is that for every cell that should be shaded blue (CF2), if it also has the red date (CF1), it does not shade as it should for CF2. Instead it is the default white, no border. Hopefully this explains it better. I am new to the group and somewhat new to excel. Thanks.- Hide quoted text - - Show quoted text - Okay. It's A1 to G40. All will have dates except a header/title row and column a. These are dates that are entered in manually. |
Two Separate Conditions for Cond. formatting
Select your range - A1 to G40.
First condition: Formula Is =AND(A1<"",A1<TODAY()-320) Choose your format Second condition: Formula Is =MOD(ROW(),2)=1 Choose your format This will give you a problem if you're using numbers in the cells with no dates, since there's a good chance those numbers will compute to a date less then your CF1. It'll turn those cells red also. Post back if you need to use these type of numbers in your header row. You could eliminate the header row from the CF1, however Column A might present a problem if it contains numbers. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "VegasSageV" wrote in message oups.com... On Jun 26, 7:51 pm, "Ragdyer" wrote: Let's start at the top. Answer these questions.. Exactly what rows and columns are you including in the CF? Is it something like A1 to Z100 ... or what? Exactly what rows and/or columns will contain a date? Is it something like A1 to A100 ... or A1 to Z1 ... or *every cell ... or what? Are the dates keyed in, or are they the returns from formulas, or are they imported from another application? With this info I'll try to duplicate your scenario as closely as possible. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"VegasSageV" wrote in message oups.com... On Jun 26, 7:12 pm, "Ragdyer" wrote: That was the request! The date format should supercede the row format. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------*"VegasSageV" wrote in message roups.com... On Jun 26, 6:02 pm, "Ragdyer" wrote: All that's necessary is to simply enter your date formula as the *first* condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message groups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue)- Hide quoted text - - Show quoted text - Unless I am doing something wrong it isn't working. The shaded rows are picking up the conditioning on the dates I think.- Hide quoted text - - Show quoted text - I'm sorry, I did not explain it properly. I am using the following: CF1--Cell Value is, less than, =TODAY()-320" turns date Red. CF2--Formula is, =MOD(ROW(),2)=1 turns every other row shaded. What happens with the above scenario is that for every cell that should be shaded blue (CF2), if it also has the red date (CF1), it does not shade as it should for CF2. Instead it is the default white, no border. Hopefully this explains it better. I am new to the group and somewhat new to excel. Thanks.- Hide quoted text - - Show quoted text - Okay. It's A1 to G40. All will have dates except a header/title row and column a. These are dates that are entered in manually. |
Two Separate Conditions for Cond. formatting
On Jun 27, 9:03 am, "RagDyeR" wrote:
Select your range - A1 to G40. First condition: Formula Is =AND(A1<"",A1<TODAY()-320) Choose your format Second condition: Formula Is =MOD(ROW(),2)=1 Choose your format This will give you a problem if you're using numbers in the cells with no dates, since there's a good chance those numbers will compute to a date less then your CF1. It'll turn those cells red also. Post back if you need to use these type of numbers in your header row. You could eliminate the header row from the CF1, however Column A might present a problem if it contains numbers. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "VegasSageV" wrote in message oups.com... On Jun 26, 7:51 pm, "Ragdyer" wrote: Let's start at the top. Answer these questions.. Exactly what rows and columns are you including in the CF? Is it something like A1 to Z100 ... or what? Exactly what rows and/or columns will contain a date? Is it something like A1 to A100 ... or A1 to Z1 ... or *every cell ... or what? Are the dates keyed in, or are they the returns from formulas, or are they imported from another application? With this info I'll try to duplicate your scenario as closely as possible. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------*"VegasSageV" wrote in message roups.com... On Jun 26, 7:12 pm, "Ragdyer" wrote: That was the request! The date format should supercede the row format. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------**"VegasSageV" wrote in message roups.com... On Jun 26, 6:02 pm, "Ragdyer" wrote: All that's necessary is to simply enter your date formula as the *first* condition, and the row shading as your *second* condition. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message groups.com... 1st need: Shading every other row. I have this figured out. 2nd Need: (this is where problems begin) I need to CF so that the date in the cell becomes red if it is 320 days old. I am using "cell value is, less than, =TODAY()-320" It works when it's alone, but when I use it WITH the 1st (above) CF, it only works for every other row. How can I combine these two to show BOTH every other row shaded and turn red if it is older than 320 days old. Thanks for the help. (I did search, I just couldn't find this particular issue)- Hide quoted text - - Show quoted text - Unless I am doing something wrong it isn't working. The shaded rows are picking up the conditioning on the dates I think.- Hide quoted text - - Show quoted text - I'm sorry, I did not explain it properly. I am using the following: CF1--Cell Value is, less than, =TODAY()-320" turns date Red. CF2--Formula is, =MOD(ROW(),2)=1 turns every other row shaded. What happens with the above scenario is that for every cell that should be shaded blue (CF2), if it also has the red date (CF1), it does not shade as it should for CF2. Instead it is the default white, no border. Hopefully this explains it better. I am new to the group and somewhat new to excel. Thanks.- Hide quoted text - - Show quoted text - Okay. It's A1 to G40. All will have dates except a header/title row and column a. These are dates that are entered in manually.- Hide quoted text - - Show quoted text - This works, except when the date condition is true in a shaded area. |
| All times are GMT +1. The time now is 09:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com