Conditional Formatting for Specific Cells

[amoto]

Hello Everyone,

I have a spreadsheet that has several files each belonging to a different department. Column B will be a hidden and specifies the department the file belongs to. Columns G-N are the different departments, both their field and office designations.

I would like that when I list a file in Column A, Conditional Formatting uses the information from Column B to fill the two cells that pretain to the file. Basically indicating that those cells need to be updated. The next step after that would be that once I fill in the dates in the LIght Red they go back to no fill. I've attached an example for recerence.

Thanks in advance!

[amoto]

One more thing I forgot to mention. There are certain departments that receive two file types. So, as an exampl, lets say that SE and BW both go to the BW department and we'll remove the SE department from the list different departments.

[bentleybob]

As in the attached?

[amoto]

Yes, the attached file from the first post also applies to the second.

[bentleybob]

My earlier reply was to your original post, and I was asking if the solution I sent meets your needs.

[amoto]

Sorry for the delay in a response bentleybob,

The example you attached is exactly what I'm looking for per the original post! I've tried poking around the formatting to see how you came up with a solution and for the most part I can follow how you put it all together. I noticed that you have the conditional formatting going back to the column headings. If I wanted to have multiple file types go to the same department how would I go about rearranging the formulas? I tried adding a reference in the logic for the AND formula so that it would read =AND($B6=$I$3,$B6=$M$3,I6="") but that didn't work.

Any suggestions?

Thanks in advance!

[bentleybob]

I think the problem is that B6 is either I3 or M3 (or neither), but not both. Perhaps you want a combination of AND and OR, as in:

Please Login or Register  to view this content.

If that doesn't work, I might suggest you send an updated sample workbook so I can see exactly what you're up against.

[amoto]

The OR function did the trick! Thank you so much for the help!!