Format Cell Based On Another Cell's Value

[Tubtech]

In the workbook provided, is there ANY way to highlight the Cells in Columns H and V, that correspond to the highlighted cells in the Available Player List. Example being, player 1 (01) is highlighted, have cell H10 be highlighted as well and so on, and have this work if the number order changes. Any help will be appreciated! Thanks!

[6StringJazzer]

This is possible because you are using conditional formatting to do the highlighting. All you have to is extend the Applies To range for the relevant rules.

But there are a couple of problems understanding what you need.

There is nothing in column H, so either you want to highlight blank cells or your description does not match the file you provided.
The example you gave of player 1 is not helpful; it would be better to refer to actual data in your file.
Column V has merged cells so you cannot highlight it.

Also, merged cells are almost always a bad idea. You are making liberal use of them and I can't see a reason that it's needed.

You have redundant CF rules. For example, you have 5 rules for when column P is "Regular" and they all do the same thing (green fill). You can do with with one rule that affects all desired ranges. (red rectangles). Applies To range would be

Please Login or Register  to view this content.

You have another rule that checks the cell's value for "Regular" applying to columns P and Q, but it will never affect Q because Q is part of a merged cell. (blue rectangle) Not sure what you're doing with the rule above that since it only applies to once cell in column Y, which is empty.

new CF.jpg

[6StringJazzer]

Here is your file with a redesigned sheet that does not use merged cells. I can fix your highlighting once it is clearer what you need.

[Tubtech]

OMG ! I just finished a 36-hour shift and posted the wrong workbook, I'm an idiot! Here is the correct workbook I need help with. Sorry!

[6StringJazzer]

I'll take a look.

My advice about not using merged cells still applies to this file. I see no reason to do that in this file.

Also the same issue with your conditional formatting rules.

Under what circumstances would values in column P or L ever be "X"? Is that an error in your CF rules?

I'll attach a corrected file with the additional coloring you are asking for shortly.

[Tubtech]

The X's are imported from another folder so I know who is available to play the current week. I use that "X" as the rule to highlight the player name.

[6StringJazzer]

I understand what the X's are for, but you have conditional formatting rules based on having X in columns L and P, which have player numbers in them. I wonder if those rules are in error.

Here is a fixed version, see the second sheet.

[Tubtech]

Thanks, I see what you did! I appreciate the help!

[6StringJazzer]

Glad it helped! Thanks for the rep.