Formatting, formatting, formatting

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
0
nickdangr1 (14)
2/21/2005 6:42:17 PM
excel 39879 articles. 2 followers. Follow

3 Replies
385 Views

Similar Articles

[PageSpeed] 57

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!
==============================================


"NickDangr" <nickdangr@myexcel.com> wrote in message
news:MPG.1c83f590105d15f5989686@msnews.microsoft.com...
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

0
ragdyer1 (4060)
2/21/2005 8:42:07 PM
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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"NickDangr" <nickdangr@myexcel.com> wrote in message 
news:MPG.1c83f590105d15f5989686@msnews.microsoft.com...
>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 


0
nothanks4548 (968)
2/21/2005 8:50:56 PM
As always, you folks are 'da bomb.

Regards

Ben/ND

In article <MPG.1c83f590105d15f5989686@msnews.microsoft.com>, 
nickdangr@myexcel.com says...
> 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
> 
0
nickdangr1 (14)
2/22/2005 6:48:19 PM
Reply:

Similar Artilces:

Changing Form background color conditional format
I am trying to fingue out how to get the conditional format for a Form. There is a field called [Reason For Test] and I am trying to write the code so that when someone chooses "Applicant" the form page (not continuous) background color is Green, and when they choose "Allied Agency" the form page background color is blue. I would really appreciate help with this. Forms, to my knowledge, don't have a conditional format property. You'll need to add a small bit of code to the After_Update event of this control ([Reason For Test]) to change the backgro...

Format Credit Card Number on Receipt
I am trying to format the credit card number on my receipts to place a space or a dash between the four digit groups. Are there any vbScript or XML functions that can be placed into the receipt template to format with? THanks Jamie ...

Excel 2000-Accounting Underline for Custom or Style Format
I can build a style or custom format to get 125,345.22 to display as 125 and -125,345.22 to display as (125) in red, but when I do so, I lose the accounting underline. When I say "accounting underline," I mean the underline that underlines across the cell, not just under the numbers. Can anyone help get the accounting underline to work? Even if I go back and select Accounting Underline in Format / Cells, the full accounting underline doesn't happen. Thanks, Mike --- Message posted from http://www.ExcelForum.com/ ...

Date format #4
how do i change the date format from xx/xx/xxxx (which I guess must be the default) to xx/xx/xx? I have Money Small Business 2004. Sorry I failed to mention that I'm referring to the Accounts register. Charley "BiggoCharley" <cca@deltaforge.com> wrote in message news:M0Thd.26097$Qv5.557@newssvr33.news.prodigy.com... > how do i change the date format from xx/xx/xxxx (which I guess must be the > default) to xx/xx/xx? I have Money Small Business 2004. > Money uses the dates from the computer it is installed on, so you should change it in Control Panel->...

Custom format that shows blank cell if another cell is empty
I have a formula in A1, just referrencing to B1 (=B1) I need to setup following custom format to A1: If B1 is blank, show blank in A1 If B1 is is 0, show 0 in A1. If B1 is positive, show the positive in A1. I have a problem, that blank and zero behave same, so when B1 is blank, I get always 0 instead of blank. Is it possible using customer format, not formula (if-then)? Thank you Zdenek Moravec Formula will do it =IF(B1="","",IF(B1=0,0,IF(B1>0,"+ve",""))) -- HTH RP (remove nothere from the email address if mailing direct) "Zdenek Moravec&...

Formatting a drop down list
The source from my drop down list has intentional strikethroughs on some of the entry fonts. However when i click on the drop down list, the strikethroughs don't show up. How can I have the font effects show up on the drop down list? Anybody know how to do this? "Dyefrog" wrote: > The source from my drop down list has intentional strikethroughs on some of > the entry fonts. However when i click on the drop down list, the > strikethroughs don't show up. How can I have the font effects show up on the > drop down list? I don't think that t...

Is there way to see 7 day calendar in same format as 5 day calendar?
I'm using Outlook 2003. I'd like to be able to see a week at a time in the calendar view but want to see it in the format like the 5 day week where you see the hours down the left side. My monitor is big enough that even thought there will be 7 columns it will still be usable. Currently if you choose 7 WEEK (or if you drag across 7 days on the mini calendar on the left side) it goes to the view where each day is a box with no time bars....like a bigger version of the month view...I hate that view. Thanks In that mini calendar, don't select the 7 days in the same week....

Outlook 2000 format confirmation msg
Hi, I installed Outlk2000 in WinXP Pro a few days ago, afte upgrading form another OS. Since the beginning, every time I send an email in HTML, I get a message from Outlk asking me to confirm the format, warning me that perhaps the email will not be read by some people if sent in HTML. No option to stop asking this in the future. I want to stop this message. Any help would be appreciated. Thanks! ...

conditional formating for the whole row, based on one cell value
I have applied conditional formating to cell G6, when I choose value "Closed" from drop down menu for this cell, it gets huighlighted in grey. I would like the range A6:J6 to get highlighted in grey as well. How do I apply this part in Excel-2003? Thank you! 1. Select the cell/Range (say A6:J6) 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and enter the below formula =$G6="Closed" 4. Click Format Button>Pattern and select your color (say Grey) 5. Hit OK -- Jacob "Renata" wrote:...

More than 3 conditions in conditional formatting
I'm trying to use conditional formatting for a certain column, but as soon as I put in 3 conditions, the add button grays out I want: >= 0 to be black 0>x>=-7 to be red -7>x>=-21 to be yellow <-21 to be green Any ideas?? Thanks Make cell start black by default First conditions is formula =AND(A1<0,A1>=-7) and your color red Second condition is formula =AND(A1<-7,A1>=-21) and your color yellow Third condition is formula =A1<-21 and your color green. A1 is of course the cell to be verified. Use format brush to copy this format to all cells in the colum...

Conditional Formatting #80
How do I conditionally format some cells so that the ones containing a date that comes before today are highlighted? I've tried everything I can think of! (Mind you, that's not much) Under conditional formatting select "Formula Is" and enter this: =A1<(ROUND((NOW()),0)) where A1 is the cell with the date in it. Lee >-----Original Message----- >How do I conditionally format some cells so that the ones containing a date >that comes before today are highlighted? I've tried everything I can think >of! (Mind you, that's not much) >. > ...

Is it possible to format *individual* gridlines?
I would like to make the 0 line (X-axis) bold, to emphasise when my graphed line drops below zero, but it seems as though I can only format all of the gridlines together. Is it possible to format a single gridline alone? Hi, No you can not format individual gridlines. To get the effect you can use a dummy series and format that. See here for more information. http://peltiertech.com/Excel/Charts/AddLine.html Cheers Andy newaglish wrote: > I would like to make the 0 line (X-axis) bold, to emphasise when my graphed > line drops below zero, but it seems as though I can only format a...

Convert "Date Format" and into "Text"
I have a query like this: -------------------------------------------------------------------------------- SELECT Name, B_Day, FROM people -------------------------------------------------------------------------------- my "B_Day" output looks like this: 12/4/1985 12/4/1995 -------------------------------------------------------------------------------- I would like the output to be "yyyy-mm-dd" (1985-4-12), and I would like it to be "text". Thanks a lot , experts ! ^_^! -- Allen Phailat Wongakanit What's the data type of B_Day: Text or Date? If it'...

Custom receipt format
How do I get a full page Invoice to say invoice instead of receipt? It was that way but changed for no known reason. Are you using the Full Page Invoice template from CustomerSource? What does it say instead of 'Invoice'? I use a modified version of that file, and I've noticed that any zero-dollar transaction prints 'Sales Receipt' instead of 'Invoice'. Tom "RWBouchard" wrote: > How do I get a full page Invoice to say invoice instead of receipt? It was > that way but changed for no known reason. The file is called fullpage.xml. I do not...

AutoCorrect
In my AutoCorrect window next to Replace and With, why are the options plain text and formatted text greyed out? "Formatted text" is available only if you have selected formatted text before opening the dialog (or pasted in text that has characters that require formatting). "Formatted text" means any text that contains characters from more than one font or that has, say, italic or bold formatting applied. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Elizabeth" <Elizabeth@discus...

Importing from web page-format
Hello :). I am trying to import a table from a web-page where some of the values are written for example as 7 -35. I am making a querry for importing the table into excel. Then the 7 - 35 value in transformed into the number12966. I understand this have to do with the way excel is reading dates. But 7 - 35 in the table is not meant to be a date, but showing two different numbers (7 and 35). I therefore have tryed to go to format, numbers and set different formats to the cell to make it show exactly the number om the webpage (7 - 35). The closest i was to make it work is to set the format defi...

number format
how can I get Indain numbers in Microsoft Publisher Have you tried finding an Indian font? There are several Indian numbering systems. You might look at the Arial Unicode symbols as well. There is a free Brahmi font download here: http://www.omniglot.com/writing/brahmi.htm -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "xxhudhudxx" <xxhudhudxx@discussions.microsoft.com> wrote in message news:F7972C4A-5EC6-4FD9-AF3F-00EEE2DE19F5@microsoft.com... > how can I get Indain numbers in Microsoft Publisher ...

Formula Dependant Conditional Formatting
I am wanting conditional formatting on an entire column of data (B). Each cell's format is dependent on the data in the cell directly to its left (A). When I select the Conditional Formatting for B1 and enter the formula to make it dependant on A1, it works fine. I then copy the formatting down the row. The only problem is that the format in all the cells are dependant on A1, not their respective cells from column A. Is there a way to copy the conditional format down a column that will cause the format's formula to change respectively like formulas in the cells themselves do? m...

conditional formating #11
Here is what I want it to happen: There is a number in cell A1 and a text in A2. When I change the number in A1 I want the "text" change in A2. How can I do this with conditional formatting? I know how to change the "format" of A2, but what I want is a change of the text in A2. um... why not just use an "IF" statement in A2...? On May 12, 10:55=A0am, "minimus" <mini...@live.co.uk> wrote: > Here is what I want it to happen: > > There is a number in cell A1 and a text in A2. When I change the number i= n > A1 I want the "tex...

MIssing Formatting and standard toolbars when Word launches
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Myself (and it seems a lot of othe rpeople including some I know) are having this issue all of a sudden: <br><br>When Word is launched the formatting and toolbars are missing. They are checked in the toolbar menu so they should appear. I have tried to reset them and that does nothing. Also, when you uncheck the standard tool bar and then re check it only one item appears with an arrow next to it so then I uncheck it and re check it again and then it appears. With formatting it only works if I unc...

sc2 file format
how would i install a sc2 schedule plus file in outlook 2007, i have about 2000 users with a sc2 file that was imported from a groupwise migration and all we have installed on the desktop is outlook 2003. is there a command line utility to convert or an addon to outlook 2007. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Ne...

Formula for formatting rows and columns minimizing the outcome.
I am using Excel 2007. I have 3 columns A, B, and C such as below: TU10-10 TU10 Tungsten Satin Wedding Band TU10-10.5 TU10 Tungsten Satin Wedding Band TU10-11 TU10 Tungsten Satin Wedding Band TU10-11.5 TU10 Tungsten Satin Wedding Band TU10-12 TU10 Tungsten Satin Wedding Band TU10-12.5 TU10 Tungsten Satin Wedding Band TU10-13 TU10 Tungsten Satin Wedding Band TU10-6 TU10 Tungsten Satin Wedding Band TU10-6.5 TU10 Tungsten Satin Wedding Band TU10-7 TU10 Tungsten Satin Wedding Band TU10-7.5 TU10 Tungsten Satin Wedding Band TU10-8 TU10 Tungsten Satin Wedding Band TU10-8.5 TU10 Tung...

copy format and formulas of one report to a second report
I need to copy a report to create a second report, and am looking to know how to globally change formulas to a different table/query. The fields in the two tables are identical, the data results are different. Example of a Current formula: =Sum(IIf([Customer Sat Results database]![The service or information you received was appropriate and help]="Good",1,0)) New report formula would be: =Sum(IIf([Internet Customer Sat Results database Query1]![The service or information you received was appropriate and help]="Good",1,0)) there are about 96 formulas t...

How to create Conditional Formats that "grow" with copy/fill down.
I would like to set up 1 conditional format that will grow as I copy & paste. All I've been able to do is copy the same over & over. I have tried to "paste special - format", but it didn't work. Example: Cell value is: equal to, $F$8, then Font is White. I would like to grow the "$F$8" as I copy & paste. Thanks for ANY help. Try a formula like =A1=$F$8:F8 -- HTH RP (remove nothere from the email address if mailing direct) "AliceNXLand" <AliceNXLand@discussions.microsoft.com> wrote in message news:70AD36AA-FAF1-49E3-8BCF-...

Mixed formats on dates
I have a spreadsheet that has a date column and the users have mixed the formats. The column is formated as a date as m/yy. But somehow, there are entries of numbers in a similar format. Those display like 01/02. If I try to change the format to text, then those fields that are a date turn to a 5digit number, those that are numbers stay in their format. Is there a way to correct these into one or the other format? I am trying to import this into an Access database and it keeps creating an error. The problem is the mixed format. (I am aware of the trick of saving a spreadsheet as a webpage a...