Using conditional formatting with Table as reference ?

[MasterBash]

Hi,

I have a table with 3 columns to be used as conditional formatting, using the table header as the color for the conditional formatting.

Unfortunately, when I add conditional formatting to my A column and use each of the named range as reference, all it does is format based on the first row below the table header.

Validation OTM vs RP test.xlsx
	A	B	C	D	E
1	7416165		Column1	Column2	Column3
2	4984791		18871365	7788141654	22226666
3	22226666		8846451	99763146	46411V016A88
4	891919		29984433	2634169	419647982
5	46411V016A88				MR6497226
6	MR6497226				97141942V47
7	29984433				44556987
8	4547944165				99988847
9	18871365
10	2113377916
11	16916149
12	97141942V47
13	469499
14	MR6497226
15	8846451
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=$A1=Column3	text	NO
A:A	Expression	=$A1=Column2	text	NO
A:A	Expression	=$A1=Column1	text	NO

Because more data may be added to the table, I need to use dynamic range for conditional formatting.

Is it possible to use Conditional formatting the way I want to ?

Thank you !

 

[Kaper]

if you just compare one cell (current row in columnA with a named range, which ois a vertical range of few cells excel just compares your Ax with first cell of that vertical range - as you already noted. so in formatting condition try counting how many times Ax occured in that range, and if count is more than 0, then there is a matching value.

so in a formatting rule instead of

=$A1=Column1

try

=countif(Column3,$A1)>0

and so on

 

[djclements]

Instead of using =$A1=Column1, they either of the following formulas:

=COUNTIF(Column1,$A1)
=AND(NOT(ISBLANK($A1)),OR($A1=Column1))
=AND(NOT(ISBLANK($A1)),ISNUMBER(XMATCH($A1,Column1)))

COUNTIF will exclude blank cells, whereas OR and ISNUMBER-XMATCH will not, which is why the additional NOT-ISBLANK criteria is needed.

Do the same for the rules using Column2 and Column3.

 

[MasterBash]

Thank you !