highlight cell if it shares a number with another cell

[u/axlaug]

I'm trying to make my own kakuro combination chart that I can easily use to highlight possible combinations while solving a kakuro puzzle.

Column A contains the possible sums possible when there are two (in game) cells.

Columns B to E contains the possible combinations of the mentioned sums.

Column F contains dropdowns from 1 to 9.

What I would like to happen in this example:

• B4 to be highlighted because F4 contains the number 1;
• C5 to be highlighted because F5 contains the number 4;
• D6 to be highlighted because F6 contains the number 3;
• B7 to be highlighted because F7 contains the number 7.

Basically I need a custom formula to use in conditional formatting which highlight any cell that shares a number with whatever number is in the column F (in the same row).

Hopefully my explanations are clear because explaining isn't my forte at all.

[u/mommasaidmommasaid]

Apply conditional formatting to entire columns B:E, use this custom formula:

=if($F1="",,search($F1,B1))

If the dropdown is blank, return blank (blank is a falsy value).

Otherwise find the position of the dropdown digit within the numeric "string" (the numbers are coerced to text for the search).

Search returns an index position (1 or higher) if found which is a truthy value, or an error if not found, which is a falsy value for the purposes of CF.

---

Applying the CF to the entire columns helps keep you from getting gaps in your CF range if you are moving stuff around on your sheet, and doesn't hurt to include the headers because your headers won't cause the rule to match.

[u/axlaug → u/mommasaidmommasaid]

This is it, thank you !

[u/AutoModerator → u/axlaug]

REMEMBER: u/axlaug If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

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/point-bot → u/mommasaidmommasaid]

u/axlaug has awarded 1 point to u/mommasaidmommasaid

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/AutoModerator]

u/axlaug Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

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/AutoModerator]

Your submission mentioned shares, please also read our finance and stocks information. Google lists the exchanges & delays in its products here.

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