Formatting, formatting, formatting

  • Thread starter Thread starter NickDangr
  • Start date Start date
N

NickDangr

I apologise if this has been inquired of previously, I wasn't able to
phrase my searches well enough (due largely to ignorance of Excel's
capabilities) to find anything useful on the web.

The Issue
--
I'd like to format certain rows based on the content of a cell within
the row.

Below is a sample of row data.

052159000011 6 YOGURT,OG,WM,PLAIN 32 OZ 1 Case NO
052159004682 12 YOGURT,OG,WM,STWBRY&CREAM 6 OZ 12 Case YES
052159004668 12 YOGURT,OG,WM,VAN TRUFFLE 6 OZ 12 Case YES
052159004613 12 YOGURT,OG,WM,WLD BLUEBERY 6 OZ 1 EA NO
036638222118 6 YOGURT,PLAIN 32 OZ 1 EA NO
027434011297 1 YOHIMBE FUEL 50 CAP 1 EA YES
798311111712 1 Y-SNORE NASAL SPRAY 20 ML 1 EA YES
728229678984 12 YUKON GOLD BARBECUE 5 OZ 1 EA NO
728229678946 12 YUKON GOLD ONION & GARLIC 5 OZ 1 EA NO


When column 2 in a row matches column 5, the IF formula calculates the
value of the last row as YES. When 2 is not equal to 5, it calculates
as NO.

I've found that using conditional formatting I can format one single
cell - for example, coloring the NOs as bright bold red, and the YESs as
bright bold green.

Where I'd really like to go with this is to hilight the entire row when
the value is not equal.

Any insights would be appreciated.

Regards

Ben/ND
 
All you really had to do was simply select the row while you were entering
your Conditional Format.

Say Column F contains the "Yes" and "No" return of your IF() formula.

Select A2 to F2, then:
<Format> <Conditional Format>,
Click "Formula Is",
Enter
=$F2="No"
Set your formats, then <OK>
Click <Add> for condition2, and again click "Formula Is", then:
=$F2="Yes"
And set the format for this, then <OK> <OK>.

Test the formatting.
If all OK, then select A2:F2 again, and *double click* on the Format Painter
icon on the toolbar (Yellow Paintbrush).
Now, just click and drag across all the columns and rows that you wish to
have this same format.
When done, don't forget to hit <Esc> to *de-activate* the format painter.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I apologise if this has been inquired of previously, I wasn't able to
phrase my searches well enough (due largely to ignorance of Excel's
capabilities) to find anything useful on the web.

The Issue
--
I'd like to format certain rows based on the content of a cell within
the row.

Below is a sample of row data.

052159000011 6 YOGURT,OG,WM,PLAIN 32 OZ 1 Case NO
052159004682 12 YOGURT,OG,WM,STWBRY&CREAM 6 OZ 12 Case YES
052159004668 12 YOGURT,OG,WM,VAN TRUFFLE 6 OZ 12 Case YES
052159004613 12 YOGURT,OG,WM,WLD BLUEBERY 6 OZ 1 EA NO
036638222118 6 YOGURT,PLAIN 32 OZ 1 EA NO
027434011297 1 YOHIMBE FUEL 50 CAP 1 EA YES
798311111712 1 Y-SNORE NASAL SPRAY 20 ML 1 EA YES
728229678984 12 YUKON GOLD BARBECUE 5 OZ 1 EA NO
728229678946 12 YUKON GOLD ONION & GARLIC 5 OZ 1 EA NO


When column 2 in a row matches column 5, the IF formula calculates the
value of the last row as YES. When 2 is not equal to 5, it calculates
as NO.

I've found that using conditional formatting I can format one single
cell - for example, coloring the NOs as bright bold red, and the YESs as
bright bold green.

Where I'd really like to go with this is to hilight the entire row when
the value is not equal.

Any insights would be appreciated.

Regards

Ben/ND
 
Nick,

You need to use "Formula is," instead of "Cell value is." The secret is in
the fact that the formula you put in applies to the active (white) cell of
your selection, and is copied to other cells of your selection with the
usual adjustments for relative cell references.

Select the rows. Format - Conditional formatting - Formula is.
= $H2 = "no"

This is for the case where the active cell is in row 2, and presumes your IF
is in column H. I wasn't sure, with all the commas and spaces in your data
where the columns fell.

If you want to get more resourceful, you can put the IF right in the
conditional formatting formula, and do away with the column containing your
IF.
 
Back
Top