Forum Discussion
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.
What exactly do you want to match to what? A sample workbook would help.
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
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.
- Excellent the countif has done the job, thank you! I did not try the isnumber formula. Many thanks again
