Hey Gang,

I have a fairly generic calendar that I use as a team duty roster for about
30 people, which becomes quite busy as the months assignments are scheduled.
What would help me immensely as I balance the assignments, is the ability to
change the cell color based on a search criteria.

For example, I have T. Brown assigned on a Wednesday, Saturday, and
Thursday, and B. Evans assigned on a Tuesday, Thursday and Friday, and E.
Nicholas on.... etc, etc, etc..

What I would like, is the ability to enter the persons name in cell A1 (for
example), and anywhere within the range of the worksheet, the cells that
match with the persons name in A1 would change color. If A1 remains blank,
then no action is taken.

I'm currently using a countif statement to count all the occurrences which
helps with totals, (=COUNTIF(Sheet1!$A$14:$AA$89,"E. Nicholas")) but I
think being able to color code as I assign and change duties would really be
helpful.

Any help is appreciated.

Thanks!
David

One way:

Select A14:AA89, with A14 active. Choose Format/Conditional
Formatting... Set the dropdowns and input box to read

CF1: Formula is =AND($A$1<>"",A14=$A$1)
Format1: <pattern>/<your choice of color>



In article <(E-Mail Removed)>,
"DavidJ726" <(E-Mail Removed)> wrote:

> I have a fairly generic calendar that I use as a team duty roster for about
> 30 people, which becomes quite busy as the months assignments are scheduled.
> What would help me immensely as I balance the assignments, is the ability to
> change the cell color based on a search criteria.
>
> For example, I have T. Brown assigned on a Wednesday, Saturday, and
> Thursday, and B. Evans assigned on a Tuesday, Thursday and Friday, and E.
> Nicholas on.... etc, etc, etc..
>
> What I would like, is the ability to enter the persons name in cell A1 (for
> example), and anywhere within the range of the worksheet, the cells that
> match with the persons name in A1 would change color. If A1 remains blank,
> then no action is taken.
>
> I'm currently using a countif statement to count all the occurrences which
> helps with totals, (=COUNTIF(Sheet1!$A$14:$AA$89,"E. Nicholas")) but I
> think being able to color code as I assign and change duties would really be
> helpful.

I am trying to do the same thing and was wondering is it possible to have
more then 3 different values for the formating...when I get to the third one
the add button greys out....I need to have 4 values for the cells....

If it is possible anything would help...

Thanks...

Keith
"JE McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> One way:
>
> Select A14:AA89, with A14 active. Choose Format/Conditional
> Formatting... Set the dropdowns and input box to read
>
> CF1: Formula is =AND($A$1<>"",A14=$A$1)
> Format1: <pattern>/<your choice of color>
>
>
>
> In article <(E-Mail Removed)>,
> "DavidJ726" <(E-Mail Removed)> wrote:
>
>> I have a fairly generic calendar that I use as a team duty roster for
>> about
>> 30 people, which becomes quite busy as the months assignments are
>> scheduled.
>> What would help me immensely as I balance the assignments, is the ability
>> to
>> change the cell color based on a search criteria.
>>
>> For example, I have T. Brown assigned on a Wednesday, Saturday, and
>> Thursday, and B. Evans assigned on a Tuesday, Thursday and Friday, and E.
>> Nicholas on.... etc, etc, etc..
>>
>> What I would like, is the ability to enter the persons name in cell A1
>> (for
>> example), and anywhere within the range of the worksheet, the cells that
>> match with the persons name in A1 would change color. If A1 remains
>> blank,
>> then no action is taken.
>>
>> I'm currently using a countif statement to count all the occurrences
>> which
>> helps with totals, (=COUNTIF(Sheet1!$A$14:$AA$89,"E. Nicholas")) but I
>> think being able to color code as I assign and change duties would really
>> be
>> helpful.

If one of the 4 is a default color, then use it for all your cells, then
apply CF for the other 3 values.

If you have four values + your default, you'll need to use VBA.

In article <(E-Mail Removed)>,
"Keith Bradley" <(E-Mail Removed)> wrote:

> I am trying to do the same thing and was wondering is it possible to have
> more then 3 different values for the formating...when I get to the third one
> the add button greys out....I need to have 4 values for the cells....
>
> If it is possible anything would help...

Can your suggest a good site to learn how to do scripts...I have only did
very little programing in vba (mostly in dos based programming and c++)
never in vba....

Thanks again for all the help...

Keith
"JE McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> If one of the 4 is a default color, then use it for all your cells, then
> apply CF for the other 3 values.
>
> If you have four values + your default, you'll need to use VBA.
>
> In article <(E-Mail Removed)>,
> "Keith Bradley" <(E-Mail Removed)> wrote:
>
>> I am trying to do the same thing and was wondering is it possible to have
>> more then 3 different values for the formating...when I get to the third
>> one
>> the add button greys out....I need to have 4 values for the cells....
>>
>> If it is possible anything would help...