SOLVED

# Conditional formating using formula

Copper Contributor

# Conditional formating using formula

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

4 Replies

# Re: Conditional formating using formula

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

# Re: Conditional formating using formula

@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

best response confirmed by Jna3276 (Copper Contributor)
Solution

# Re: Conditional formating using formula

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.

# Re: Conditional formating using formula

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

Accepted Solutions
best response confirmed by Jna3276 (Copper Contributor)
Solution

# Re: Conditional formating using formula

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.