Excel conditional format only if all cells contain values?
I have a calendar in Excel, and we can't allow more than a certain amount of
staff off during a given amount of time. I have a few cells that comprise a single day, and listing staff names on each one of those lines. I'm trying to figure out a conditional formula that will shade the entire "day" a certain color if all of the cells contain value. For example: 4/1/09 is cells C11:C15. Using the formula below, if any value is entered on any one of those cells, it highlights the entire group of the cells with the color specified. =COUNTA($C$11:$C$15) Can anybody direct me as to how I can get it so that ALL of the cells must contain a value, for any of them to be shaded red? Meaning, something MUST be entered in C11, to C15, then all of the cells will then change in color? Thanks! |
Excel conditional format only if all cells contain values?
Highlight all the cells, the open the Conditional Formatting and use this :
Condition1: Formula Is: =COUNTA($C$11:$C$15)=5 -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "offdah3z" wrote: I have a calendar in Excel, and we can't allow more than a certain amount of staff off during a given amount of time. I have a few cells that comprise a single day, and listing staff names on each one of those lines. I'm trying to figure out a conditional formula that will shade the entire "day" a certain color if all of the cells contain value. For example: 4/1/09 is cells C11:C15. Using the formula below, if any value is entered on any one of those cells, it highlights the entire group of the cells with the color specified. =COUNTA($C$11:$C$15) Can anybody direct me as to how I can get it so that ALL of the cells must contain a value, for any of them to be shaded red? Meaning, something MUST be entered in C11, to C15, then all of the cells will then change in color? Thanks! |
Excel conditional format only if all cells contain values?
THANK YOU! I was pulling out my hair for "=5"?! You're a lifesaver and I
learned something new! "JBeaucaire" wrote: Highlight all the cells, the open the Conditional Formatting and use this : Condition1: Formula Is: =COUNTA($C$11:$C$15)=5 -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "offdah3z" wrote: I have a calendar in Excel, and we can't allow more than a certain amount of staff off during a given amount of time. I have a few cells that comprise a single day, and listing staff names on each one of those lines. I'm trying to figure out a conditional formula that will shade the entire "day" a certain color if all of the cells contain value. For example: 4/1/09 is cells C11:C15. Using the formula below, if any value is entered on any one of those cells, it highlights the entire group of the cells with the color specified. =COUNTA($C$11:$C$15) Can anybody direct me as to how I can get it so that ALL of the cells must contain a value, for any of them to be shaded red? Meaning, something MUST be entered in C11, to C15, then all of the cells will then change in color? Thanks! |
Excel conditional format only if all cells contain values?
One more question, how can I that to be greater than 4? I've tried a million
things. =COUNTA($C$11:$C$15)=4 for some reason, Excel hates that, and thats the most logical thing I've tried. i'm embarrassed to show the rest. "JBeaucaire" wrote: Highlight all the cells, the open the Conditional Formatting and use this : Condition1: Formula Is: =COUNTA($C$11:$C$15)=5 -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "offdah3z" wrote: I have a calendar in Excel, and we can't allow more than a certain amount of staff off during a given amount of time. I have a few cells that comprise a single day, and listing staff names on each one of those lines. I'm trying to figure out a conditional formula that will shade the entire "day" a certain color if all of the cells contain value. For example: 4/1/09 is cells C11:C15. Using the formula below, if any value is entered on any one of those cells, it highlights the entire group of the cells with the color specified. =COUNTA($C$11:$C$15) Can anybody direct me as to how I can get it so that ALL of the cells must contain a value, for any of them to be shaded red? Meaning, something MUST be entered in C11, to C15, then all of the cells will then change in color? Thanks! |
Excel conditional format only if all cells contain values?
If you want greater than 4 it is =COUNTA($C$11:$C$15)4
If you want greater than or equal to 4 it is =COUNTA($C$11:$C$15)=4 -- David Biddulph "offdah3z" wrote in message ... One more question, how can I that to be greater than 4? I've tried a million things. =COUNTA($C$11:$C$15)=4 for some reason, Excel hates that, and thats the most logical thing I've tried. i'm embarrassed to show the rest. .... |
All times are GMT +1. The time now is 08:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com