Conditional formatting for rows

[ExcelObsessed]

I'm familiar with conditional formatting, but cannot figure out the formula
to highlight an entire row based on the contents in one cell. For example,
for every cell in column G that contains the word "visa", I would like that
entire corresponding row to be highlighted. Can anyone tell me the formula?
Thanks!

 

[Luke M]

Formula is: =IF($G1="visa",true,false)
Do this somewhere in row 1, then copy the format everywhere you need it. The
$ sign locks the formula on column G, while the row number is floating.

 

[ExcelObsessed]

Thank you, but I'm asking specifically about conditional formatting. So,
going into format --> conditional formatting, I need to enter a formula to
automatically highlight the entire row rather than just the individual cell.
Thoughts? Thanks again!

 

[Luke M]

That is the formula to use with the conditional format. You can copy
conditional formats from one cell to another.

Once you have the conditional format in once cell, copy, then select desired
area(or all, with Ctrl+A) to be affected, and right-click, paste special.
Choose format.

 

[ExcelObsessed]

That worked! I was trying to format the whole range at once, but pasting
special as the format fixed that. Thanks!!

 

[Luke M]

Glad it worked! Thanks for the feedback.

 

[Pete_UK]

Highlight row 2 by clicking on the row identifier, then click on
Format | Conditional formatting, select Formula Is rather than Cell
Value Is and enter this formula:

=ISNUMBER(SEARCH("visa",$G2))

Then click on the Format button, select the Patterns tab and choose
your background colour. OK your way out. With row2 still highlighted,
double-click the Format Painter icon, then click in A3, A4, A5 etc for
all the rows that you want this effect to apply to - press <Esc> when
you are done.

Hope this helps.

Pete

 

[Gord Dibben]

You could simply select Column G then Format>CF>Cell Value is: Equal to "visa"
without the IF, TRUE, FALSE formula.

Or simply Formula is: =$G1="visa"

Also without using the format painter.


Gord Dibben MS Excel MVP