Conditional formatting of a cell to change its fill color based on text any of several cel

Greetings all. Ok, I haven't found anything that matches my situation, so my apologies if I've missed it. Here goes:
I am trying to create a menu calendar for my family. In column P, I have all of the possible entree items we might use in the month. What I'm trying to do is set up each of the cells in column P (P1 through P40) so that if any of those items is typed into the calendar (in very specific cells for what it's worth), the respective item in column P will change fill color. So let's say spaghetti is in P2 (P1 is the title of the column). If we have spaghetti on say the 12th of the month, it would be typed into cell H21 (the dinner location on the calendar for that particular date). So basically if the text in H21=spaghetti, I want cell P2 to change fill color to red. Or if we have hamburgers instead on the 12th, H21 would = hamburgers, and cell P25 (which already has the word hamburgers in it) would turn red. Make sense? Can it be done? Keep any answers SIMPLE, step-by-step please (for instance I have no idea what =AND($P5$I21)="true" means). I know where conditional formatting is, but that's about it. Excel 2007. THANKS!!!

Hi and welcome to the forum

I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.


And while you are about it, please change your thread title to something that actually describes your problem
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I would recommend using a COUNTIF for this. If the meals are in P2:P40 and the columns where you might enter one of them is columns A:H, you could use this CF formula


=COUNTIF(A:H, P2)>0

And while you are about it, please change your thread title to something that actually describes your problem
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Please comply with this before we help you further

1) Press F1 in Excel and read the builtin tutorials on Conditional Formatting, always the best step 1

2) Highlight P6:P100

3) Open the Conditional Formatting window and select NEW RULE

4) Select USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT

5) Enter this formula: =COUNTIF(A:O, P6)>0

6) Click Format > Fill and select a color.

http://screencast.com/t/jvgD0sufZ40M

You can possibly check your File > Options > Formulas > Workbook Calculation and make sure it's set to Automatic.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.