I have a column of cells formatted with different colors to represent a range of values. I want to know if that a formula or VBA I can use to I sum up these range of cells with the same color.
View 10 RepliesI was wondering if there's a way to make the numbers in a column over 50 (as an example) green and numbers less than 50 red. (The numbers in this column are the sums of other cells, but I guess that doesn't really matter.)
This way when there's a lot of things going on you can see what's what without filtering or rearranging.
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
I have a free form scribble in Excel that I would like to change colors with VBA code, or even from a cell reference, either one would be ok. Is there a way to do this?
View 10 Replies View RelatedI have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
I have values in a number of cells. Next to them, if there was a value, regardless of what the value is, except blank or zero, i would like the cell to turn blue and if possible have a 1 in the cell.
I've used the conditional formatting, however, among the options, 'less than' is the closest to to getting the color right. The issue is that if the cell has nothing in it, it still colors the corresponding cell blue; because it is not less than the already blank cell. If it is blank, I don't want it to color the cell.
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
http://img530.imageshack.us/img530/6239/excelspfk0.jpg
I have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)
View 9 Replies View RelatedI’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
I want to add across columns, which is not so difficult but I have to "ignore" the blank cells because of the way my formula works.
I'm not sure of the correct mathematical term for the addition I have to do but here is my example:
in this row are the values
1 2 3 4 5
In the row below I want to add the numbers so the result is
1 3 6 10 15
basically, a cell is always adding itself to the result of the sum from the previous cells. This formula I can handle, (=A2+B1 then autofill the results by dragging the formula across the empty cells) but when there are blank cells between values I get the error message! value. How can I write a formula which will ignore the empty cells?
i have a problem adding cells every 5th cell for example i have
=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67),5)=0))
this adds every 5th row in this range and it does it right as long as there is no text in the cells in between
it works fine when it looks like this
1
1
1
1
1
1
1
but it does not work when it looks like this
1
1
text
1
1
1
when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them
i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....
creating this code or fomatting in my excel file. I want to fill color in cells between the two cells that has value of say "1" or anything in it. There will be only 2 numbers at any time in any row that has the value in it. All other cells will have value of "0" or can be changed to empty cells. So, the formula should check from say range "A3 to Z3" in row and for example it gets the first numbered cell in C3 then it start filling color from C3 cell until it reaches the other cell in that range that has the number in it. Say the row "A3 to Z3" has 1 number in C3 and other in cell H3 then it should fill color between this two cells from C3,D3,E3,F3,G3 and H3.
View 7 Replies View RelatedI have a 2 lists of manually entered values where the values in each row are related. They are related in the sense that if the difference between them is equal to or less than, a given amount then the cell containing one value is colored Blue. e.g.
COl A holds Value x while COl B holds value y. I need to check which value is entered in columns A and B and if a given difference or greater exists colour the cell in COl B. For example the relationship relevant to a COL A value of 21.0 is 11.0.
So, I enter the following;
A1 B1
21.0 13.0 <----------------No color Blue
I enter new values
21.0 9.5 <----------------B1 is blue.........................
I would like to add a date to one cell (say A6) and have this do two things:
#1) this would add "Closed" to a given cell such a A5.
#2) and this would add a color to a group of cells like A1 through A8.
I m adding cell with alphabets. I attach a pic of what i want it to look like. Is there a command to do it for you instead of me outting one by one myself? Ex. 'A' must first followed by 'B' and so on, futhermore, if there a CApital 'A' and a lower case 'a' the capital 'A' goes first.
View 14 Replies View Relatedevery month i download a sales list of which there are about 200 transactions.
each day there is a different amount of sales ie
feb 1st 9.99
feb 1st 14.99
feb 1st 7.99 (3 sales)
feb 2nd 8.99
feb 2nd 16.99
feb 2nd 14.99
feb 2nd 8.99 (4 sales)
"" "" "" ""
etc throughout the month
at the moment i am using the SUM to maunually add the sales per day
feb 1st 32.97
feb 2nd 49.96
as i need a daily record of income, however this is very time consuming
is there any way i can request the worksheet to add say column b for the 1st feb and show total
column b for 2nd feb and show total, thus saving myself hours of work ??
way to add up the total for each day
Here is what I have In cell H4 I put the following code =B5:G5
But when I add number in thous cells nothing happens. I want cell H4 to keep a running total of cells B5 through G5 so as I add numbers to the cells I would like the total in H4 to change.
All I want to do is the following;
I have a workbook with 18 columns and 9000rows for each column,
All I want to be able to do is ad a"," (Comma) in each cell at the end of what ever is in each cell.
Tried looking in the help of excel, but if you can't write in properly what you want, it doesn't give you the answer of how to do this in the help list
While cells(r,40) or cells(r,41) are empty error 13 occures
Dim r
r = ActiveCell.Row
Cells(r, 43) = Me.TextBox3.Value
Cells(r, 41) = Cells(r, 41) + Cells(r, 43)
Cells(r, 42) = Cells(r, 40) - Cells(r, 41)
Why doesn't excel "see" empty cells as null?
I have three columns.
For instance, Column A are names. Column B are totals points and Column C is year.
I want to a cell too look up all names of "John" for the year 2007, then with those two conditions add up all total points for John in 2007.
I am trying to add a ROW of times. Each cell of times is using the 00":"00 format so I don't have to type a colon between the hours:minutes.
I'm not sure of what the formula should be for the Total Hours box.
For example, if I were to type 13:45 in the start time & 14:00 in the end time, I am getting a result of 0:55 which is incorrect
Another example if I leave the first 2 start/end boxes empty(blank) and then type 15:30 Start & 17:00 in the second set of Start/end boxes, I get a Total Hours of 1:70 which is also incorrect.
The formula I have in the Total Hours box is =SUM(B12-A12)+(D12-C12) which is know is incorrect.
What is the correct formula which will give me a Total Hours which adds a row of times correctly?
StartEndStartEndTotal Hours
I have a worksheet that has 5 rows and 5 columns. The five rows are Highschool subjects. Math, English, French, Phys. Ed, History. The Five columns are Monday, Tuesday, Wednesday, Thursday, Friday. I want to device a worksheet that say for Monday it will have YES for math, no for english, yes for French, yes for phys. ed, no for history. Tuesday would be say no for math, yes for english, no for french, etc, etc. I then want to use an if statement to convert the yes & no entries to a numeric value. I.E I have this right now. =If(B6="YES",1,0)
This simply puts a 1 for a yes and 0 for a no into cell M6. What I want tho is each subject to add the amount of yes's for the week and put it into cell M6. So if math is taken 3 times a week it would put a 3 into cell m6. if History is taken 4 times a week it would put 4 into cell m7, etc, etc.
1) I am trying to find a way to add together the total number of hours worked from shift start time to the end of the shift start time throughout the week.
2) Add together the total number of hours travelled. again from the start and end times throughout the week...(presumably, if I solve the first problem, it in itself will provide the second solution.!! (Grin)
Where shift start and end times are concerned, the cells are formatted as time, but I want the sheet to produce the results of the total number of hours worked, so that I may cost the hours and make payment. Ie: 62 hours worked = £ at such and such a rate, and 25hrs travel paid at £ different rate.
in a cell range a1:a5 I have the following data .5v .25s 1v .75s .5v. I want to add all numbers with v and s separately so that in cell a6 I can have totals of v i.e. 2v and in cell a7 1s
View 2 Replies View RelatedI recieve cost sheets from a company and I want to be able to change there number into a formula and mark it up all in one. How would I do this without going into each cell individually and make the change? Example say the number is $100 in the cell I want to change that to =$100/.6 to get a 40% margin. Then I can give this to salesmen with just the retail number but I can see the formula and cost.
View 9 Replies View Related