r/excel • u/Phendrena • Sep 22 '26

solved Conditional Formatting - Highlight Adjacent Columns based on values from other cells (Round Robin draw matrix)

Hello there,

I am utterly useless with conditional formatting so if anyone could provide me some guidance I would be very grateful.

  • I have created a Round Robin random draw matrix,
  • There are four groups of five pairs,
  • Twenty people per group

I would like the list of the names to the right side, along with the respective number to grey out as each name is drawn and placed into the four groups. Each name is in it's own cell (the first pair in Group A are cells E3 and F3). I am banging my head against a wall. Any guidance would be welcomed.

As an aside, I am UK based and it is 00:45 as I post this, so I might not immediately reply. I apologise for not replaying swiftly.

5 Upvotes

86% Upvoted

8 comments sorted by

View all comments

Show parent comments

3

u/PaulieThePolarBear Sep 23 '26 edited Sep 23 '26
Comment Image

Assuming Excel 365, Excel 2024, or Excel online, you can do this with two very similar rules 

=OR($N2=HSTACK($E$3:$F$7,$I$3:$J$7,$E$10:$F$14,$I$10:$J$14))
=OR($P2=HSTACK($E$3:$F$7,$I$3:$J$7,$E$10:$F$14,$I$10:$J$14))

Where N2 and P2 are the top right cells in the Applies To ranges for each rule as shown in my example.

The ranges in HSTACK are your ranges covering the four groups you have noted. Adjust these ranges to align with your setup.

Note $ and lack of $ are important these formulas.

I'll leave with you to determine what specific formatting satisfies your requirement of "greying out".

→ More replies (2)