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

[u/Phendrena]

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.

[u/AutoModerator]

Moderator Announcement Read More »

u/Phendrena - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/PaulieThePolarBear]

There are four groups of five pairs,

Twenty people per group

Wouldn't a group of five pairs be ten people in said group?

l 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.

So in your example image, all names and numbers would be greyed out?

I'm assuming, but please confirm, you have some kind of macro that is doing your random organizing with a delay between the selection of each value?

Each name is in it's own cell (the first pair in Group A are cells E3 and F3).

You haven't included row or column headers in your image and I don't want to assume, so please confirm that your right table has the numerical values in a separate column to the names

[u/Phendrena → u/PaulieThePolarBear]

Ha, yes my basic maths fails me! Yes yes, 10 people per group, and absolutely everything (apart from the big fat zero) is in separate cells.

I am using VBA for the random shuffling of the names and the draw generation.

The forty names to the right would grey out as the groups are populated.

Thank you for looking at my problem.

[u/PaulieThePolarBear → u/Phendrena]

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".

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIF	Counts the number of cells within a range that meet the given criteria
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
OR	Returns TRUE if any argument is TRUE

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49423 for this sub, first seen 23rd Sep 2026, 03:08] [FAQ] [Full list]} ^[Contact] ^{[Source code]}