Color fill based on multiple conditions

My spreadsheet contains subtotals.

A                                  B       C     D    E   F 
Production Line 1            10     1     5    2   10
Production Line 1            10     1     5    2   10
Production Line 1 Total    20     2    10   4   20

Some months the spreadsheet contains 100 lines and some months it contains 
200.  Does someone have some code that would loop through all the data in the 
sheet and for each subtotaled line (when A contains Total), go to the same 
row for B:F and if value is
less than 5, fill with orange
between 6 and 15, fill with blue
greater than 15, fill with green

I am using Excel 2003.

Blue Angel
0
Utf
3/15/2010 4:18:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
773 Views

Similar Articles

[PageSpeed] 32

In A2:A10 I entered some text; a few had the word "Total"
In B2:F10 I entered some numbers
I select B2:F10 and in the Conditional Formatting dialog used
Formula IS: =AND($A2="Total", B2<5)
Format fill orange
Note the $ on A2. Because I selected down to row 10, the conditional 
formatting applies to those rows also (so row 5 is =AND($A5="Total", B5<5) )
In pre-Excel2007 you can have up to 3 conditions
For more on the topic go to
See http://www.contextures.com/xlCondFormat01.html

best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"C" <C@discussions.microsoft.com> wrote in message 
news:AC0A4956-B0FA-4829-9D21-67680EAB13DB@microsoft.com...
> My spreadsheet contains subtotals.
>
> A                                  B       C     D    E   F
> Production Line 1            10     1     5    2   10
> Production Line 1            10     1     5    2   10
> Production Line 1 Total    20     2    10   4   20
>
> Some months the spreadsheet contains 100 lines and some months it contains
> 200.  Does someone have some code that would loop through all the data in 
> the
> sheet and for each subtotaled line (when A contains Total), go to the same
> row for B:F and if value is
> less than 5, fill with orange
> between 6 and 15, fill with blue
> greater than 15, fill with green
>
> I am using Excel 2003.
>
> Blue Angel 

0
Bernard
3/15/2010 4:51:11 PM
Reply:

Similar Artilces:

Replacing values based on a set list
Hi Could anyone help me with this one: - I have an Excel workbook with two worksheets: sheet1 and sheet2. On sheet1, I have 2 columns of data:- Column 1 - CityID Column2 - CityName On sheet2, I have a list of 500 office locations, some of which are in the same city. Sheet2 has several columns, one of which is the city location of the office. I am trying to prepare the Excel sheet ready to import into Access and want to replace each instance of the city location listed on sheet2 with the CityID number from sheet1 where the city is matched. For example: - Sheet 1 CityID CityName 1...

Icon Colors
I just installed Office 2003 and I want to change the color of the icon that > I am pointing to in Excel. In addition the color of the cell locator A, 1 for example > is the same obnoxious orange color. Where do go to to change these colors > without changing the XP color scheme (XP style, silver)? -- Profkay Dr. Kay You do not change these colors without changing the Windows color scheme or theme. Gord Dibben MS Excel MVP On Sat, 7 Apr 2007 04:10:00 -0700, Dr. Kay <DrKay@discussions.microsoft.com> wrote: > I just installed Office 2003 and I want to change the ...

Changing Names of Multiple Cell Link Ranges
I have a series of drop down boxes created with the Forms menu. The cell link range for each dropdown is different, as follows: Cell Link Box1 = DemandBase_A_UndistExp1 Cell Link Box2 = DemandBase_A_UndistExp2 Cell Link Box3 = DemandBase_A_UndistExp3 Cell Link Box...n = DemandBase_A_UndistExp...n I want to change them all at once to: DemandBase_A_OtherCost1 DemandBase_A_OtherCost2 DemandBase_A_OtherCost... I know I can change the entire name using the following code: Sub Change_Drop_Link() For Each bx In ActiveSheet.DropDowns If bx.LinkedCell = "DemandBase_A_UndistExp1" Then ...

Copying record value from column in multiple sheets into single sh
Hi, I have some employee Records in sheets "Nov07","Dec07" and "Jan08". the first two columns(A and B) are employee number and name in each sheet, and column C is their overtime. I want to create a consolidated sheet "YTD" so that I can see all their YTD overtime in one sheet and do some analysis. Hence the new sheet will have first two columns A and B same as above howver column C, D and E will be the overtime from each sheet. It would have been a simple copy paste if the number of employee had been static, but it changes every months as new em...

Changing chart background color in Excel 97 SR 2
I am fairly new to this so please bear with me. I have a chart based on numbers in a spread that has a grey background (The chart) but I would like the background to be white and can not get it to change. I right clicked on the chart and went to Format Chart Area, Patterns, Fill Effects, Patterns and changed the background to white but the ok button is greyed out. Am using Excel (Office) 97 SR 2 TIA http://members.aol.com/larrystark/ Right click (or double click) on the gray region, which is the Plot Area. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tut...

create rule based on the DISPLAYED from field
Hi Group, I'm using Outlook 2007. Is it possible to create a rule that searches the text displayed in the "from" field? I would like to delete messages with certain offensive words in the "from" field, but I'm unable to find any rule that does that. I can see the words of "interest" if I go to Message options / Internet headers, but how do you build a rule based on that ? thank you, andy use a 'words in the header' rule. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchan...

count cell with background color 36
First I want to count the number of cells in a range that have text in them. Then I want to count how many of these cells have the back ground color 36 Oldjay This will achieve what you want for a continuous range, Column A in this example. Change the variables to suit. Take care Marcus Option Explicit Sub MyCount() Dim lw As Integer Dim counter As Integer Dim MyConstant As Integer Dim i As Integer lw = Range("A" & Rows.Count).End(xlUp).Row counter = 0 MyConstant = Range("A2" & lw).SpecialCells(xlCellTypeConstants).Count MsgBox "The...

how do I concatenate color attributes with data
hello excel experts, I have a frustrating problem...I would like to find out if there is way to attach the format of the data within cells when using th concatenate function. For example: if cell A1 had a bold 3 in it, an A2 had a regular 3 in it, then the result of the concatenation functio of those two cells would be a cell with one bold 3 and a regular 3 i it. Does that make sense??? Help Please!! -- Message posted from http://www.ExcelForum.com Hi not possible with formulas. Formulas can only return values but not formats -- Regards Frank Kabel Frankfurt, Germany > hello exc...

Sum by multiple
Dear all, I need the help on below; Colunm1 0.9 0.89 0.87 I need to show my results as 0.69687 (0.9*0.89*0.87) in query. I don't think Access provides a way to multiple the values in a column, so you will probably need to write a VBA function. OpenRecordset() and loop through the values to get the product. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "sq75222" <sq75222@discussions.microsoft.com> wrote in message news:193A32B1-1EEC-46FC-9...

how to change the color of the sheet tab
Hi everybody How can I programaticly change the color of the sheet Tab (the place where the sheet name appears) thanks Just an example: ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 28 In general: just do it manually and record your actions. Than inspect the recorded macro and change where needed. -- Kind regards, Niek Otten Microsoft MVP - Excel "yosi_lb" <yosi_lb@bezeqint.net> wrote in message news:45be0a67@news.bezeqint.net... | Hi everybody | How can I programaticly change the color of the sheet Tab (the place where | the sheet name appears) | |...

Pdf/Multiple Pages/& Publisher
Hi, I created a catalog merge in Publisher. The merge creates 100 different postcards and they are printing 4 different postcards to a page. My problem is the printers cannot access a publisher file, so I'm trying to convert it to a pdf. I have a pdf writer so that's not a big deal, I use it all the time. For some reason though this time when I print to pdf, it's creating 12 different pdf documents instead of one. The printer is charging us $4.00 to open every file and I don't want to pay more to open my files then it will cost me to have them printed. Any ideas on...

Need help printing multiple pages in VB.Net 2008
I am having a problem printing multiple pages with a common header/footer etc in VB 2008. I have the following code which will print a single page exactly as I need it. '***************** Print button click event ************ Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click pdPrintCal.PrinterSettings.PrinterName = lblCurPrt.Text pdPrintCal.DefaultPageSettings.Landscape = True If chkPrtPView.Checked Then pvPrintCal.Document = pdPrintCal pvPrintCal.Icon = Me....

5.5 multiple domains
If our company sets up another little branch within our office but wants a separate email address for those guys (in addition to an email address for them from the main site) do I understand this correctly? We'll pretend the original company is called 'Sprockets Inc' and they are at sprockets.com We're a single site org (sprockets) and decide to start making cogs (yes, I'm an 80s child) so we register cogs.com Our ISP currently has mx(10) sprockets.com 200.20.20.50 (mail.sprockets.com) We ask them to add mx(10) cogs.com 200.20.20.50 (mail.cogs.com) In exchange 5.5 admin...

merging over multiple pages
This might be a basic question, but is it possible to merge over multiple pages (like in word)? I couldn't find any merge rules like in Word (i.e. <<Next Record>>). I want to create a catalog that (A5 format on landscape A4 pages) would run over multiple pages and be able to print the result back to back correctly so that the pages can just be stapled together and folded book style and read p1, p2, p3 etc. Is this possible? Kind Regards Damo What you have described is the way a catalog merge works. Setup your publication as a booklet, use the catalog merge. Lots of hel...

Allow multiple selections within a checkbox of items
How do I create the option within a form to select more than one option within a field? Shawna, Use checkboxes rather than option buttons. Option buttons are limited to one selection per frame - checkboxes are not. HTH, Bernie MS Excel MVP "shawna" <shawna@discussions.microsoft.com> wrote in message news:C447FF94-8454-4DC1-ADC2-3E0DDD7EFD61@microsoft.com... > How do I create the option within a form to select more than one option > within a field? ...

Pass Message to Base Class Message Handler
Can I relay message to base class message handler ? If I have base class derived from some other class e.g CWnd then how can I handle base class message handlers ?? If I call base class PreTranslateMessage manually its not getting invoked...... e.g In My derived class PreTranslateMessage if I invoke CBaseClass::PreTranslateMessage(pMsg).... base class pretranslatemessage is not getting invoked....? -Akshay Did you make the function "virtual" in your derived class? You could also try calling with GetParent() if it is a window. Tom <akshay4friend@yahoo.com> wrote in mes...

Multiple E-Mails
Hi I have just installed SBS2003 and everything seems to work O.K, but one mailbox keeps receiving muliple e-mails from the same address. Where should I start looking to resolve. Hi Richard, Is this a SPAM message or an error which is causing the email to be resent? If it is SPAM sent from the same address just add the sender to the Sender Filter list In Exchange System Manager | Message Delivery, and enable/apply the sender filter in the following location: Exchange System Manager | Administrative Groups | Admin Group Name | Server Name | Protocols | SMTP | Right Click Properties on D...

multiple email addresses for contact
Is there any way to modify the email address field to allow multiple email addresses for one contact? Thanks hello, you can add custom attributes to the entity contact, so you can, for example, create fields like "new_email2", "new_email3" and show them on the contact form. "lynn" wrote: > Is there any way to modify the email address field to allow multiple email > addresses for one contact? > > Thanks But can you then select that email address for "To"? This seems also to be an issue with Spouse Email and Personal Email, built in...

Sending to multiple addresses
How do I send an email to multiple addresses having only the recipient see their own email address? Michelle wrote: > How do I send an email to multiple addresses having only > the recipient see their own email address? Put the addresses into the BCC: field. Note that many e-mail programs will decide such mails are spam though. >-----Original Message----- >How do I send an email to multiple addresses having only >the recipient see their own email address? >. Open your new email and go to tools, select recipient, when your address book opens select (highlight) the a...

[Fwd: Re: New computer. Newer version of Windows. Printing color Differences]
FYI Do you really print on monitor? I don't! Your assumption is way out of kilter! hth JoAnn Paules wrote: > > New computer - new monitor I assume? > > http://www.wikihow.com/Calibrate-Your-Monitor > > -- > JoAnn Paules > MVP Microsoft [Publisher] > Tech Editor for "Microsoft Publisher 2007 For Dummies" > > "Reddawg2566" <Reddawg2566@discussions.microsoft.com> wrote in message > news:AB9C9A32-5EEA-4A24-92C6-BBB2426E3552@microsoft.com... > >I just bought a new computer with Windows 7 a...

Hiding cells based on the value in another cell
Hi, I am using Excel 2007. I have created a worksheet where I would like to be able to "hide" a group of cells based on the value of another cell. I can do what I want by inserting a shape (rectangle) and giving it a white background and a white line color. As I have chosen not to show gridlines on my worksheet, this rectangle effectively hides the cells that are behind it. I can write code to control the visibility of this rectangle based on the value in a particular cell. I imagine that there is a way I can get this code to run anytime the value in this cell...

12x Focusing Loupe Magnifier with Acrylic Base
Price:$19.95 Image: http://discountadvisor.info/image.php?id=B000TWSZXS Best deal: http://discountadvisor.info/index.php?id=B000TWSZXS See every dot with this precision instrument. Clear, acrylic base sits flat on table or work surface and suspends the 12-power coated three-element lens above your work. Includes deluxe, snap-lock case.Lens Diameter: 5/8Viewing Area: 1/2" diam.Overall Size: 1-1/2" x 4-1/4" x 1-1/4"Country of Origin: China ...

access data base on LAN / WAN ?
Can any one please give one idea/ fundamental need of knowledge / material ..as im trying to place my access - 03 data base on one computer( remote place) and update / retrive data from many computers ,how to design such data base for many users please. your help will change the way of my working with access thank u The most important thing you can do when ever you setup an access db to run over a lan is to split your db an issuing a copy of the front-end to each user. You can get some more information at http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm -Splitting your ...

Coloring CComboBox
Hi all, I have a subclass of CComboBox with the following CTL_COLOR method (like MSDN): HBRUSH CColorComboBox::OnCtlColor(CDC * pDC, CWnd * pWnd, UINT nCtlColor) { if (nCtlColor == CTLCOLOR_EDIT) { if (m_edit.GetSafeHwnd() == NULL) { m_edit.SubclassWindow(pWnd->GetSafeHwnd()); } } else if (nCtlColor == CTLCOLOR_LISTBOX) { //ListBox control if (m_listbox.GetSafeHwnd() == NULL) { m_listbox.SubclassWindow(pWnd->GetSafeHwnd()); } } HBRUSH hbr = CComboBox::OnCtlColor(pDC, pWnd, nCtlColor); return hbr; } The coloring works fine if the dialog is opened with DoMo...

Any IF(A2=font color=red", "1", "0") in excel? Or a way to do it?
Function GetColorindex(rng As Range) GetColindex = rng.Interior.Colorindex End Function =If(GetColorindex(A2)=3,1,0) -- HTH RP (remove nothere from the email address if mailing direct) "Steve" <Steve@discussions.microsoft.com> wrote in message news:57BCF230-A4CF-4D76-9E1E-D94B99B54235@microsoft.com... > ...