Is there a way to use Conditional Formatting on a row based on values in a single column?

Question

Possible Duplicate:
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?

Answer 1

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 ;-) .

Edit:

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

Answer 2

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:

=AND($D1="cat")

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).

Answer 3

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.

Answer 4

I'm not sure the existing answers are as clear as they could be...

Excel XP/2002/2003

1. Select the row(s) you wish the formatting to apply to
2. Click Format>>Conditional Formatting
3. Change Condition 1 to "Formula is"
4. Enter formula, referencing only the test cell but with an absolute reference ($) on the column component and the top row in your selection as the row, e.g. =IF($C4>100,True,False) if you have rows 4+ selected and want to change the entire row if the cell in the fourth column, C, is over 100
5. Set your format in the normal way

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.

Answer 5

Finally I've found a simple solution and will share with You.

Writing a formula like this: =$H1="Open" or 1,2 then specify the range from Applies to:$1 to $1048576 here is the H1 contains the conditional value.

Thanks for every effort and every comment...