conditional formatting using formula

[lezawang]

Hi I want to format (change the color) of any row which has word "left" in the second column.
I highlighted everything and went to CF-->Formula and wrote this formula

=A1="left"

But that only highlighted the cell in 3rd column which has value = left. I want to highlight the whole row (3 cells). Thank you.

[TABLE="width: 400"]
<tbody>[TR]
[TD]name[/TD]
[TD]dept[/TD]
[TD]salary[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]it[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD]hr[/TD]
[TD]left[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]sales[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]dan[/TD]
[TD]marketing[/TD]
[TD]left[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

First of all "left" is in the 3rd column of your data not the second.

I'm assuming jim is in A2, adjust to fit

Select A2:C5

Conditional Formatting
New Rule
Use a formula to determine...

=($C2="left")

Format as required

 

[lezawang]

Works perfectly. Thank you so much. Could you please tell me if you do not mind how did that work? do the ( ) have anything to do with that? Thank you once again.

First of all "left" is in the 3rd column of your data not the second.

I'm assuming jim is in A2, adjust to fit

Select A2:C5

Conditional Formatting
New Rule
Use a formula to determine...

=($C2="left")

Format as required

 

[Joe4]

No, it has nothing to do with the parentheses, but everything to do with the $. The is called an absolute reference, and locks that column down, so as you move across other columns, that reference never changes.
To learn more about absolute, relative, and mixed references, read this here: http://www.excel-easy.com/functions/cell-references.html

Also, another option to using the format painter to apply it to a multi-cell range is to first select the entire range you want this Conditional Formatting to run against, and then write the formula as it pertains to the very FIRST cell in the selection. If you have set your range references correctly (locking the proper ones with absolute references, if necessary), Excel will automatically adjust it for all the other cells.