Conditional formating using formula

[Jna3276]

Hi,

I'm looking to apply a conditional format to a table (Table1) which highlights the row where a cell matches a cell within another table (Table2)

I've had a look online, the only thing I can find is a formula which works if I refer to an array of cells rather than another table in the workbook:

=MATCH(A2,Array1,0)

This only highlights a single cell, even if I try to apply the conditional format to the Table1

Can anyone help?

Thanks

[#2]

@Jna3276 

What exactly do you want to match to what? A sample workbook would help.

[#3]

@HansVogelaar hi I have attached a file, sheet 2 has the cells I'm trying to conditional format against, a table or the cells in column A

Sheet 1, column A is where I'd like to apply the conditional format, eg. If cell A1 matched the data in sheet 2 then I'd like it to highlight the entire row where A1 matches in sheet 1

Conditional format to fill any colour 

Hope that makes sence

[#4 · Accepted solution]

@Jna3276 

Select the data rows of the table on Sheet 1 (i.e. A2:G8).

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

=ISNUMBER(XMATCH($A2, Sheet2!$C$2:$C$5))

or

=COUNTIF(Sheet2!$C$2:$C$5, $A2)

You can't use structured table references in conditional formatting rules. But Excel will automatically adjust the rule when the table expands/shrinks.

[#5]

Excellent the countif has done the job, thank you! I did not try the isnumber formula. Many thanks again