Conditional Formatting in Excel 2007

Hi all-  
  Can someone give me detailed directions on how to format a column so that 
the cells with 4's in them are highlighted yellow, the cells with 5's in them 
are red and the cells with 6's are blue.  I have tried using the wizard but 
the numbers 1-3 get highlighted also.  Thanks in advance.
LabLawrence
0
Utf
12/29/2009 7:06:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
948 Views

Similar Articles

[PageSpeed] 38

Select the cells
Open the Conditional Format dialog, use Highlight Rules -> Equal To;
in the left had box enter 4, in the right-hand select a colour combination 
as needed
Repeat for 5.
best wishes
-- 
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"LabLawrence" <LabLawrence@discussions.microsoft.com> wrote in message 
news:EE452828-F97D-4C90-9790-FD49A69347AA@microsoft.com...
> Hi all-
>  Can someone give me detailed directions on how to format a column so that
> the cells with 4's in them are highlighted yellow, the cells with 5's in 
> them
> are red and the cells with 6's are blue.  I have tried using the wizard 
> but
> the numbers 1-3 get highlighted also.  Thanks in advance.
> LabLawrence 

0
Bernard
12/29/2009 7:49:08 PM
In EXCEL 2007:-

1. Highlight the range of cells to which you wish Cond Form to apply (eg M1 
to M71). 

2. Home / Conditional Formatting / New Rule 

New Formatting Rule pane should launch. 

3. Select:-

Only cells that contain

4. Change:-

between

 - to:-

equal to 

 - and put a 4 in the box to the right. 

5. Hit Format so that the Format Cells pane launches. 

6. Change the;-

Color: 

 - field to yellow and hit OK and OK again. 

7. Repeat steps 2 to 6 above inclusive but changing the number 4 to 5 and 6 
and the colours to red and blue respectively. 

You should now have 3 rules set up for the range specified at number 1 above 
(M1 to M71). 

8. Test the rules by entering data in the range M1 to M71 and making sure 
that the right colours are returned. 

I have tested the above and looks OK to me. 

If my comments have helped please hit Yes. 

Thanks. 



"LabLawrence" wrote:

> Hi all-  
>   Can someone give me detailed directions on how to format a column so that 
> the cells with 4's in them are highlighted yellow, the cells with 5's in them 
> are red and the cells with 6's are blue.  I have tried using the wizard but 
> the numbers 1-3 get highlighted also.  Thanks in advance.
> LabLawrence
0
Utf
12/29/2009 7:55:02 PM
Reply:

Similar Artilces:

Conditional formatting 01-05-10
Hello, I am trying to create a spreadsheet that lists the classes I need to take in order to graduate, and have them highlighted based on whether or not I have completed the prerequisites (which are in the list as well). I have a small list setup for testing, and created checkboxes linked to cells on another sheet whether the row (class) has been completed. How would I go about highlighting the classes that do not have the prerequisites met in red and the non-completed classes ready to take in green? Any help is appreciated. ...

Create business form in Excel
I am a long time user of basic Excel functions. My employer would like to create Estimate/Proposal forms in Excel. I am not sure how to have some parts of the worksheet display with grid lines, and have other areas on the worksheet display without grid lines. If there is a video tutorial and you can send me a link that would be greatly appriciated. I do know how to merge cells and outline cells. I have been trying to figure this out for myself for the last week and have become frustrated. Thank you in advance. -- maagk Most of the people who respond to questions in these news group...

Can i use conditional formating on a cell when it contains a formula? #2
=mid(text(a1;"0000000000.00");1;1 -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi the reason for this is that thie formula returns a string and not a numeric value. Change the formula to: =--mid(text(a1;"0000000000.00");1;1) -- Regards Frank Kabel Frankfurt, Germany sit wrote: > =mid(text(a1;"0000000000.00");1;1) OR ... Change your Conditional formula to: =&quo...

Need an Excel formula
Hello, Can someone please tell me how to make a formula in Excel that can calculate 20% multiplied by (Gross pay) minus dependance, multiplied by 38.46. -- Looking for nothing, yet searching for everything. =((gross_pay*20%)-dependence)*38.46 change the names with the cells holding the values. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Kirschwasser" <azureferal@yahoo.com> wrote in message news:FF4ACBC1-C8F8-4F98-A3F6-3DEEC7DB43C8@microsoft.com... > Hello, > > Can someone please tell me how to make a formula ...

Conditional Formatting Problem #4
Below is an example spreadsheet that I’m dealing with. I have a column with “Standards” (more on that later), but I have my results in three possible ‘formats’ “Standard” Result 1 Result 2 Result 3 0.01 0.123 0.123J <0.123 1.0 0.123 0.123J <0.123 First, I want to create a conditional format that will BOLD only the cells that have just numbers in them (i.e. 0.123), and leave the other two types alone. Second, I will be comparing the cells with only numbers in them (i.e. Result 1) to the Standard and highlighting only those values that are greater to or equal to the Standard. In...

Excel 2003 won't open spreadsheet with a chart
When a customer tries to open a spreadsheet with a chart, he gets the error, "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience. The information you were working on might be lost. Microsoft Excel can try to recoved it for you." If he tries to recover, the sheet opens and the data is there, but the charts are blank. Spreadsheets without charts open with no problem. Identical computers on the network can open the spreadsheets he can't, and he created them. This just started today. He has tried rebooting and moving the file...

Not recognized format
I've written large workbooks with excel xp, but at home I am using excel 2000. Is there any way to convert the xls file format that xp uses so I can work with the spreadsheet at home? Thanks, Alan There's no difference in file format for this version (xl97 to xl2003 share the same fileformat). There may be some features/macros that won't work because of the difference--but you should be able to open the workbooks. SKLwater wrote: > > I've written large workbooks with excel xp, but at home I am using excel > 2000. Is there any way to convert the xls file form...

HELP: Excel changed all my hyperlinks A:
Help For some reason Excel went and changed all my hyperlinks to point to my floppy drive a:\ -WHY? Is there any way I can automatically remove the a:\ hyperlink references, manually renaming is going to take ages. Excel's replace function doesn't seem to work with hyperlinks :-( -Arska Hello Arska, Have you tried find and replace? Press ctrl+h Find what: A:\ Replace with: C:\My Documents (assuming this is where the file you want to link) Then hit Replace All or if your using xl 2002 you can go to EDIT|links then hit change source Hope this could help Jon-jon "Ars...

CRM 3.0 phone number formatting
Hello all, We recently upgraded from CRM 1.2 to CRM 3.0. When entering in Phone numbers and mobile numbers in 1.2 we entered in the data as 1-XXX-XXX-XXXX ext XXXX. Now when I try to enter that sam e data in 3.0 it tries to put in brackets and messes up the phone numbers. Anyone know how to change the formatting of these fields? Thanks in advance ------=_NextPart_0001_538874A7 Content-Type: text/plain Content-Transfer-Encoding: 7bit <JohnFairbrother@discussions.microsoft.com> wrote: > We recently upgraded from CRM 1.2 to CRM 3.0. When entering in Phone > numbers and ...

ChartObjects Insanity
Either one of us - me or Excel - is insane, or soon to become so. I have code I'm trying to convert from Excel 2003, and it's not going smoothly. Here's the current problem. I have a workbook with a number of chart sheets, each containing three charts. I want to delete the third chart from every sheet. I used to say.. For Each ch In wb.Charts ..... ch.ChartObjects(3).Delete Next ch Now, that tells me I have a subscript out of range. I check, and ? ch.ChartObjects.count returns a value of 3. I can return the name on the third chart too, so I try ch.ChartObject...

Outlook 2007 Mail fails to Send, stuck in Outbox
I use MSOutlook 2007. Accounts setup correctly etc. I have tested the account and it comes back fine without errors. Everything has been working fine until - I was in My Documents and chose to send a document to 'email recipient' by right clicking on the document. It went right to Outlook and appeared to be sending. It remained in the Outbox and I kept doing a send/receive. All the while it stays in the Outbox and doensn't appear in the Sent Items folder. I discovered it IS sending as the recipient received it several times. I have re-booted etc. still does this. I have dele...

Publisher 2007 #19
I center my webpage to fit a widescreen on any monitor with Publisher 2007? Thanks, Norm HELPPPPPPPPPPPPPPPPPPPPPPPPPPPP! gbsea@yahoo.ca Go here for help http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.publisher.webdesign&cat=en_US_1845e149-582a-4e62-8d76-f1c90f948f44&lang=en&cr=US -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Norm" <Norm@discussions.microsoft.com> wrote in message news:4D0C467B-689E-4E9A-91BB-3CBBB11E0A92@microsoft.com... >I cent...

Conditional sum wizard problem--Excel 2000
This is driving me crazy. Can anyone explain why this won't work with the Conditional Sum Wizard? I've used the wizard many times before and can't figure out why it doesn't work this time. Category Price Paid? Core $55.50 yes If I try to sum "Price" using the criterion Paid?="yes", it works fine (i.e. I get $55.50). If I try to sum "Price" using Category="Core", it doesn't work (I get $0.00). Both "Category" and "Paid?" are formatted as text; "Price" is formatted as currency. Thanks. Hi ...

Sort by formatting
I have a massive spreadsheet I have used a finddups macro on and have changed the formatting of cells with duplicate data. I would like to be able to sort or filter the data by conditional formatting so all the suspect duplicates can easily be copied to another spreadsheet. Hunt and peck is not practical with over 50,000 rows. Is there any way to sort/filter this data? I am currently using Excel XP and Windows XP. TIA George Hi sorting by formats is not supported by Excel. I'd use a helper column to 'tag' the respective rows and use this helper column for sorting/filtering....

Smartlist unable export to word or excel
Hello, The exact error message I get is as follows: SmartList is unable to send data to another application. Could not find file :C:Dynamics/EXP1493.exe Now this box pops up after i try and click the export to Excel or Word button in the smartlist window. The filename seems to be wonky as i did a search ont he server for a EXP1493.exe and found nothing, also looked at a dated backup of the program folder.. no Exe's with that filename. The path looks weird to me though but i will admit i don't have much experience digging into the workings of this program. We are running GP...

Excel printing problems with macros in spreadsheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have an older printer (Xerox Phaser 5400) which has been a work horse. I have just purchased a new iMac that has Leopard on it. Now, my spreadsheets that were made in 2008 Excel won't print to the Xerox if they have macros in them. Anyone have a fix on this? I uploaded the driver AGAIN from Xerox website and it doesn't fix the problem. > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3347691155_512...

Excel using =RC[-1] format and I don't want it
On this machine only, Excel will use the format =RC[-1] when I am entering a formula on the command line by hand and click on a cell to reference it in the formula. I don't know how this got on but I would like it to go off. On my other machines, it does the old familiar A1:B2 style of cell reference. I can't even search for how to fix it since I can't determine what this is called so I can turn it off. The only reference I have seen is a button that is only effective for macro creating and I am not creating a macro, just a formula, interactively, in the cell. How ...

How do I only show a range of columns/rows in Excel?
I only want to view, say, columns A thru K and rows 1 thru 23. Even if I scroll, I still don't want the sheet to go out of that range. Hi Larry Insert the following code into a module in the workbook. Press Alt+F11 to bring up the VB Editor. Insert>Module and paste the code below into the module. Change the sheet name to your sheet name if it is different to Sheet1 Sub Auto_Open() ThisWorkbook.Worksheets("Sheet1").ScrollArea = "A1:K23" End Sub Regards Roger Govier Larry Zukerman wrote: > I only want to view, say, columns A thru K and rows 1 thru 23....

Validation in Excel Sheet
Hi, I have an EXcel sheet, where in i have 2 spread sheets.The first one i display sheet where i want to get the values displayed from the secon one which has all the data. Example: When the user enters an EMPLOYEE NO, the Excel sheet has to search fo the EMPLOYEE NO stored as a list in the second spread sheet, and i found display all the details say his address,phone no etc.If not foun it should display "NOT AVAILABLE" So i need t write a formula, where in it validates the EMPLOYEE NO i the first spread sheet with that of the second spread sheet(which has long list of EMPLOYEE N...

open up excel in quattro pro?
I received an .xls spreadsheet. I don't have excel. Can I open it using quattro pro? DO I change the extension to .qpw? How do I open it? Never mind ladies and gents - I tried it and it worked. I got impatient. Sorry for any inconvenience. Zhawk "zhawk" wrote: > I received an .xls spreadsheet. I don't have excel. Can I open it using > quattro pro? DO I change the extension to .qpw? How do I open it? ...

Excel graph 3 sets of data. 2 sets as lines 1 as column?
I have data a bit like below, I am trying to do a graph, which shows the AFR as columns for A-D, but I want 'water' and 'whole' as seperate lines to show current scores across the graph. When I do the graph, I get two columns and 1 line. Does anyone know how to get to get the two lines? A B C D AFR 0.8 0.6 0.4 0.3 Water 0.5 0.5 0.5 0.5 Whole 0.3 0.3 0.3 0.3 Hi AndyN, On the chart, select the second column, right-click, select Chart Type from the context menu, select line chart. Ed Ferrero www.edferrero.com Ed Thanks for that worked a treat, I'll remember that ...

Chart formatting #3
Is there a way to shade certain areas of a line graph? I have a simple line graph extending horizontally from point 1 to 10 on the x-axis. I would like to shade the entire background area of the graph from points 2-4 and points 6-8. Is there a way to make the graph apear that the background is grayed out between these points. Thanks Chris Chris - This has been sitting here a long time... Make a combo chart. Start by locking the Y axis scale parameters (uncheck the auto boxes). Add a series to your chart which has zero values where you want no shading and <Y axis max> where ...

excel e-mail file
i rcvd an e-mail with an excel doc. attach. i do not have microsoft excel on my computer. is there any other program that will open an excel doc. >-----Original Message----- >i rcvd an e-mail with an excel doc. attach. >i do not have microsoft excel on my computer. >is there any other program that will open an excel doc. >. >never mind, i was able to open it with microsoft access. Hi there is a free Excel viewer available at http://www.microsoft.com/downloads/details.aspx?FamilyID=4EB83149-91DA- 4110-8595-4A960D3E1C7C&displaylang=EN HTH Frank anonymous@discussions.m...

Uninstalling and downgrading from 2007 to 2003
Ok, here we go. My beta just expired and I would like to downgrade back to my old office 2003. What do I need to backup? and How do I do it? I need EVERYTHING from outlook. Mails, Folders, Tasks, Notes and Calendar items. Import/Export seems to be disabled. Please help me. Thanks, emp emp <mklinge@gmail.com> wrote: > Ok, here we go. > My beta just expired and I would like to downgrade back to my old > office 2003. > What do I need to backup? and How do I do it? > > I need EVERYTHING from outlook. Mails, Folders, Tasks, Notes and > Calendar items. Since Outlo...

Excel Autocomplete?
In Excel 2000 and 2002 (Windows 2000), while I'm typing a formula into a cell, Excel frequently takes it upon itself to include a range of cells in the formula. Very annoying! For example, while typing "= B3 * 42", after I hit the "*" key, Excel adds "11:11" to what I'm typing. (I'm working on a cell in row 11.) Have I inadvertently asked Excel to do this by checking (or failing to uncheck) an option somewhere? Many thanks, Tom I don't think it's excel. I have the same problem and it's my heavy fingers. (Or my slow...