Conditional formatting based on the color of another cell.

Hello Again,

I have another question regarding conditional formatting. I would like to highlight a cell (red or yellow) with a person's name in it based on if there are similar colors in the row. If there is yellow in the row, highlight the name yellow, but if there is red in the row it trumps the yellow and highlights it red.

Is there a way to do this that doesn't require thousands of conditional formatting rules? I have roughly 200 names to go through with a number of due dates that could come up either yellow or red.

Thanks,
Jeremy

They are done by conditional formatting based on how far away they are from today's date. Less than 30 days away is Red, less than 60 days away is yellow. I tried formatting the cell with the name to include all of the cells in the row from E:O. I have tried splitting them apart using plus signs and I have tried OR statements.

I have the potential to have "blanks" in my worksheet due to someone not receiving a due date yet. By blanks, I mean a formula in the cell that comes back as blank.

Any advice? Does this make sense?

Trying to post what I did in formulas, but it keeps coming back with "HTML code not allowed."

"how far away they are from today's date." I assume you mean how far they are in the future from today's date.

If your dates are in columns E:O and your data starts in row 2 you are going to need 2 CF rules for your names that look like this:

Yellow
=MIN($E2:$O2)<TODAY()+60

Red
=MIN($E2:$O2)<TODAY()+30

in that order

Yes, our board has a filter that tries to suppress HTML attacks but sometimes it thinks math comparison operators are HTML. we have workarounds for that. Best thing is to actually attach your Excel file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

And that worked! You all are so helpful. Thanks!

You're welcome! Thanks for the rep, and thanks for marking your thread Solved.