Change cell color based on variable condition

I have a large table with 5 data columns and a sum column:

   A  B  C  D  E   F
1  2  2  2  2  2  10
2  2  8  2  2  2  16
3  1  1  6  1  1  10
4  1  2  1  2  8  14
5  1  2  3  4  5  15

The actual data is much larger (1E10).

I would like to change the color of each cell that meets some
criteria, such as it is >= 50% of the sum (F). In the table above,
that is true for B2, C3, & E4. So I;d like to turn those cells yellow
and leave the other unchanged.

I would also like to be able to change the formula in one place and
have all cells recalculate. 

Failing that, I'd like to be able to change A2:E2 and propagate down.

I looked at conditional formatting, but I don't think it takes a
formula.
0
wonmug (29)
9/25/2010 1:19:56 AM
excel 39879 articles. 2 followers. Follow

2 Replies
486 Views

Similar Articles

[PageSpeed] 42

On 24 sep, 21:19, Prof Wonmug <won...@moo.gov> wrote:
> I have a large table with 5 data columns and a sum column:
>
> =A0 =A0A =A0B =A0C =A0D =A0E =A0 F
> 1 =A02 =A02 =A02 =A02 =A02 =A010
> 2 =A02 =A08 =A02 =A02 =A02 =A016
> 3 =A01 =A01 =A06 =A01 =A01 =A010
> 4 =A01 =A02 =A01 =A02 =A08 =A014
> 5 =A01 =A02 =A03 =A04 =A05 =A015
>
> The actual data is much larger (1E10).
>
> I would like to change the color of each cell that meets some
> criteria, such as it is >=3D 50% of the sum (F). In the table above,
> that is true for B2, C3, & E4. So I;d like to turn those cells yellow
> and leave the other unchanged.
>
> I would also like to be able to change the formula in one place and
> have all cells recalculate.
>
> Failing that, I'd like to be able to change A2:E2 and propagate down.
>
> I looked at conditional formatting, but I don't think it takes a
> formula.

Hi,

Conditional formatting can do part of the job you're trying to do. It
can change the cell's color according to a formula. There's a good
exercise on http://ulearnoffice.com/excel2010/exercises/conditionalformatti=
ng.htm
that can help you. You can even copy the conditional formatting from
one cell to another.

Hope this helps

Patrice Roy
0
9/25/2010 2:37:56 PM
On Sat, 25 Sep 2010 07:37:56 -0700 (PDT), Patrice Roy
<roy.patrice@ulearnoffice.com> wrote:

>On 24 sep, 21:19, Prof Wonmug <won...@moo.gov> wrote:
>> I have a large table with 5 data columns and a sum column:
>>
>> � �A �B �C �D �E � F
>> 1 �2 �2 �2 �2 �2 �10
>> 2 �2 �8 �2 �2 �2 �16
>> 3 �1 �1 �6 �1 �1 �10
>> 4 �1 �2 �1 �2 �8 �14
>> 5 �1 �2 �3 �4 �5 �15
>>
>> The actual data is much larger (1E10).
>>
>> I would like to change the color of each cell that meets some
>> criteria, such as it is >= 50% of the sum (F). In the table above,
>> that is true for B2, C3, & E4. So I;d like to turn those cells yellow
>> and leave the other unchanged.
>>
>> I would also like to be able to change the formula in one place and
>> have all cells recalculate.
>>
>> Failing that, I'd like to be able to change A2:E2 and propagate down.
>>
>> I looked at conditional formatting, but I don't think it takes a
>> formula.
>
>Hi,
>
>Conditional formatting can do part of the job you're trying to do. It
>can change the cell's color according to a formula. There's a good
>exercise on http://ulearnoffice.com/excel2010/exercises/conditionalformatting.htm
>that can help you. You can even copy the conditional formatting from
>one cell to another.

I looked up several examples on CF, but was unable to get it to do
what I wanted. I'll check out that exmaple. Thanks.
0
wonmug (29)
9/25/2010 3:17:39 PM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Access 2003 resets form variables during debugging while the form is open
Hello, I am using MS Access 2003. (FWIW, this is about an ADP project, not MDB.) A form has a variable that is initialized (by some function call) in Form_Load() and then stays unchanged for the form's entire lifetime. (In a language more advanced than VBA, it would be a constant, not a variable; unfortunately, in VBA I cannot initialize a constant by non- constant expression). Specifically, in my case it looks like this: Private sTempFileName as String ... Private Sub Form_Load() ... sTempFileName = GenerateTempFileName(...) ... End Sub Priv...

why does sorting change a scatter plot graph?
Why does the way a spread sheet is sorted change the look of a scatter plot graph??? the graph is just a plot of two points, (X, Y) and these two points are definded by two collumns for a given row. The two collumns don't change, and the row all stays together, so why does it change where points are plotted out on the graph when you re-sort it? AndrewT420 - Usually, for an XY (Scatter) chart, with values of X in a column and corresponding values of Y in an adjacent column, for three or more points, Excel assumes (correctly) "Series in Columns." But, when you have only...

Changing Prices in HQ.
Hi, I have this little issue. I want to change the put items on promotion using the price wizard using HQ. Unfortunately if I have stores who has differents prices for a same item the wizard do not make the proper change becuase it use the price already stored in the master table. Does anyone saw this issue before? Who was solved?. Thks in advance for your help. Rgds Rodrigo Hi there, The easiest way to look after this is to not change any data on the ITEM in HQ, but to simply do the worksheet for altering the sale price and then send it to the respective stores. Then in the works...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Content of emails is changing without any reason !
Hallo I changed operating system last week. From Win XP to Win 7. Used to work with Outlook Express at full satisfaction. I could transfer most of my emails automatically with export/import features of Microsoft software. But I suddenly discover 1 very big problem (bug ???) I am used to work with several maps, and hereby go to several levels deep. Such as : Saved mails Companyname Projectname Date of action Department Activity Name of patient Different emails So sometimes maps can go several levels deep. When I check ema...

Saving toolbar changes
After spending a lot of time to customize a toolbar in Excel 2003, it disappears when opening up another file, or starting the app again. I repeatedly change it, save it as XLB, XLT, save multiple copies in every possible location...but the damn thing always defaults to its own toolbar settings. This makes toolbars almost useless. How can one insist that PPT use YOUR toolbar setting, rather than its own default Thanks. Hi Jeff, If I have a lot of tool bar changes to make, I close all the workbook that are not hidden then unhide my personal.xls from the Window menu. I don't know why...

Printing In Color
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am trying to print a document in word that is color but it is only coming out in black and white. How can I change the color settings on my printer? Word has no setting to print in color v. B/W -- that's a function of your printer & its driver software. The settings are available in the Print dialog in the Copies & Pages List, but exactly where the setting is located depends to a great extent on what printer you have. Some printers also have physical controls on the printer, itself, but that isn't common. ...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Change cannot be saved due to sharing violation
Hello I've this message while saving the excel file even if no change ha been done to the file. There is no share on this file (exclusive use) File resides on a network drive It's very disturbing Thanks for your help Vobiscu -- Vobiscu ----------------------------------------------------------------------- Vobiscum's Profile: http://www.msusenet.com/member.php?userid=245 View this thread: http://www.msusenet.com/t-187102186 http://support.microsoft.com/default.aspx?scid=kb;en-us;328170 Thanks for your answer, I will try tomorrow noo Vobiscu -- Vobiscu ----------------...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Coloring a row
I have a spreadsheet and I want to have cells colored from column A to K if cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light blue. This is for Office 2003. I can do it with conditional formating in 2007, but my work place doesn't have 2007. I did use column L and put an if statement to give a true or false in the cell depending on if the cell in col. h was empty or not. Any ideas how to get this to work? Hi John This sort of thing will work in 2003 conditional formating. In Cell A3 go to Format - conditional formattting. Formula is Paste...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...