Conditional formatting to highlight the whole row
I have a sheet to be conditionally formatted. I want to format the whole row based on key text. Is it possible?
OK, I think I got your point. You want to highlight the complete row if only 1 cell of that row matches a value, right?
Put the value you want to match in a cell somewhere (for my example, A1). Select the row(s) where you want to have the conditional formatting. In the Home section of the Ribbon, go to Conditional Formatting, then select New Rule.
In the new Formatting Rule windows, select "Use a formula to determine which cells to format". In the field "Format values where the formula is true", enter "=IF(MATCH($A$1;$13:$13;0)>0;TRUE;FALSE)". Change the $A$1 with the cell address where you put the value you want to match, eventually on another sheet. Change the $13:$13 (meaning here row 13) with the row you want to highlight. It works also for any range of cells (B4:C8 for instance).
Click the Format... button to select how you want to highlight the rows (border, fill, font, ...).
I tested it and it works (in Excel 2007). No need for a macro ;-) .
You can skip storing the value to match in a cell (A1) and hardcode it directly in the formula if you know the value won't change: =IF(MATCH(25;$13:$13;0)>0;TRUE;FALSE
Select the cells (or row) you want, go to Format -> Conditional Formatting... and add the rules you want. When you input anything in the cells, the formatting will be done automatically.
I'm not sure the existing answers are as clear as they could be...
Excel knows that you want to format the row and, due to the relative row reference, will apply this to each cell in each row that you have selected.
This is being way over analyzed = highlight the columns that are not the columns that hold the value you want identified & enter the following formula in the conditional formatting:
the above formula is assuming the value you are using as the identifier is in column d and that the value is cat.
so using this formula as the conditional formatting of columns A,B,&C, would make the format apply if the value of Column D is the word "cat" (and no, it does not highlight row 4 if cell D1 has "cat" in it, but it WILL highlight row 4 if D4 has "cat" in it).