Change Cell Background Color Based On Search Results

D

DavidJ726

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
 
J

JE McGimpsey

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

Keith Bradley

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
 
J

JE McGimpsey

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

Keith Bradley

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
 
Top