Mar 25 2020 02:50 AM - edited Mar 25 2020 02:59 AM
Hi All,
I cannot seem to find an answer for my requirements, and hope you can come to my aid please.
Data
In Column A I have the names of countries sorted randomly.
In Column B I have the names of countries sorted randomly.
Request
1. I wish to use conditional formatting to fill the cells when A is found in B.
2. I wish to use conditional formatting to fill the cells when B is not found in A.
Thanks for your feedback.
Mar 25 2020 03:53 AM
@Elias_Haddad Both can be done with custom CF rules based on a formula.
For 1:
=MATCH(active cell, column B, 0)
For 2:
=ISERROR(MATCH(active cell, column A, 0))
Mar 25 2020 04:20 AM
Mar 25 2020 04:21 AM
Mar 25 2020 04:52 AM
@Elias_Haddad You should only write the rule for the active cell, so F1:F5 should just be F1. And you need to make sure that the reference to the other region doesn't move as each cell is considered, so replace G1:G10 with $G$1:$G$10.
Mar 25 2020 04:57 AM
@Savia So I have to create 5 separate CF's to manage the range F1:F5 ? That's a pity because my original set of data has circa 200 names in each column.
Mar 25 2020 05:17 AM
Mar 25 2020 05:31 AM
@Savia Works now. Fantastic! With much appreciation, thank you.