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?

marked as duplicate by soandos, Darth Android, Mokubai, Daniel Beck, Nifle Jul 14 '12 at 15:12

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • I think you're asking whether it's possible to format an entire row, if one of the cells is containing that text. Yes you can, with a macro. You can do almost anything with a macro. Create a button on the sheet, and program it to iterate through each row and then cell in the sheet for the text value. Format the row accordingly. – user3463 Sep 10 '09 at 8:19
  • -1 for recommending someone iterate though every cell on a sheet. Bad before excel 2007, much worse now. Assuming they coded it correctly, most people would assume their excel crashed if they ran it. – DaveParillo Sep 25 '09 at 6:17
  • I stand by the suggestion. My own code searches each row and then each cell in the row (if it finds a hit) - with screen refresh off until it's done, I could find and format text on a 25MB file in under 30 seconds: Windows XP Pro, Core 2 Duo, 2GB RAM. But thanks anyway, Dave. – user3463 Sep 25 '09 at 7:39
  • Duplicate of later, but clearer question: superuser.com/q/448261/76571 – Excellll Jul 13 '12 at 19:41
  • See meta.superuser.com/q/5185/76571 – Excellll Jul 13 '12 at 19:41

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

  • Or your method would work :-). – user3463 Sep 10 '09 at 8:20
  • So that's what he meant. Good answer :). +1 – alex Sep 10 '09 at 8:33
  • I've tried a few minutes ago, but I couldn't success...I got this result: Only one cell, containing the key value formatted. May I did Wrong? – EvoWinds Sep 10 '09 at 8:51
  • =IF(MATCH($D$16;$16:$16;0)>0;TRUE;FALSE) my code – EvoWinds Sep 10 '09 at 8:54
  • The first argument of MATCH is the value with which you want to match the row. It ($D$16) must not be in the same row ($16:$16) than the row you want to highlight of course, as this will always work and always highlight the row. – Snark Sep 10 '09 at 8:57

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

  • 3
    You don't need the AND. =$D1="cat" is sufficient – DaveParillo Sep 24 '09 at 15:13

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.

  • Only in Excel 2007. The OP didn't specify the version of Excel (s)he uses. – Snark Sep 10 '09 at 7:27
  • 2
    It works in Excel 2003. I just tested it right now. – alex Sep 10 '09 at 7:28
  • Ok, my bad. Sorry. – Snark Sep 10 '09 at 7:30
  • No problem; maybe in Excel 2007 this option is more advanced. I haven't really used it. – alex Sep 10 '09 at 7:33

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.


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