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!

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!

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!

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!

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.
....