CODEWITHSUNDEEP
excelvlookupconditional-formatting
user2547757
user2547757

Reputation: 5

Excel - How to automatically highlight cells when a cell with equivalent value is highlighted

I have a "master list". We'll say it looks like so:

A | C | E

B | D | F

Next we have a larger list like so:

A | C | E

A | D | E

B | D | E

In the master list, I want to be able to highlight A or B, and all A's or B's in the column below will be subsequently highlighted the same color. Then I want to move on to C and D with the same results.

I have done some research and I believe Conditional Formatting may be the way to go here, but I'm not sure. I also thought about VLOOKUP, but I'm very new to the formula.

Upvotes: 0

Views: 2425

Answers (1)

Trashman
Trashman

Reputation: 1584

So, for the generic condition you initially asked, there are ways to do this using a VBA macro, but even they are not perfect, and they can get very complicated. In fact, there is no direct way in Excel to check to see if the color of a cell has changed (See here: Capturing Cell Color Change). I think the simplest thing to do would be to change the way you do things slightly, and it will work better for your application.

Add a row (or two, if you want a blank one for separation) above your columns. In row A, you will type the name of the winning team - then all of the values below will highlight based on whether it is the winning team or the losing team.

To do this:

  1. Highlight your entire list (I'm going to assume this range begins with A3, and your winning teams will start with A1)
  2. Select "Conditional Formatting -> Highlight Cell Rules -> Equal To"
  3. In the left box, type =A$1 (EXACTLY AS WRITTEN). In the right box, select the highlighting you want for the winning team.
  4. Click OK
  5. Select "Conditional Formatting -> Highlight Cell Rules -> More Rules
  6. In the middle box, select "not equal to" and in the right box type =A$1 (EXACTLY AS WRITTEN).
  7. Click Format... and select the highlighting you want for the losing team. 8. Click OK

When the winning team is decided, type the winners into Row A, and the highlighting will occur automatically (Note: this will assume they're all losers until you enter a winner).

FYI: The dollar sign "anchors" that column or row (row 1 in this case). So as it checks each cell, it always checks row 1, but since the A doesn't have the dollar sign, it will check the column it is in.

I think this is the best solution for your situation. If you still want to get the answer to exactly what you asked (or at least closer to it) and you're not afraid to dive into macros, let me know and I can post some code that might work for you.

Upvotes: 0

Related Questions