Forum Discussion
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
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.
i am using this formula
=$D2="OK" in conditional formatting to highlight the whole row from A to C Yellow but it is not working
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
WHEN I AM APPLYING THE CONDITIONAL FORMATION FORMULA IT IS ONLY 1 CELL, I WANT IT TO HIGHLIGHT THE WHOLE ROW THE FORMULA IT IS USING =SEARCH ("OK", C2) CONDITION IS THAT WHEN I WILL TYPE "OK"IN COLUMN D
