Excel - Conditional Formatting using only 1st Value in Range

Question

I am trying to apply color coding rules to an entire column E (E:E) using conditional rules referencing a range on a different tab, but it only color codes using the first value in the range.

So basically I want the file to say if any of the values in the column matches the 100's of values in this range color code it red and if any values this range color code orange, etc etc,

Currently using the format : format only sells with Specific Text containing = Range Name

Answer 1

Select column E.

E1 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISNUMBER(XMATCH(E1, range_name))

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.

Excel will automatically adjust E1 to E2 when it applies the rule to E2, etc.

Answer 2

How to apply to all cells E:E?

Answer 3

Create a rule of the type 'Use a formula to determine which cells to format' with formula

=ISNUMBER(XMATCH(E1, range_name))

(assuming that E1 is the active cell in the selection when you create the rule)