Assigning Bar Charts Different Colors Based On RGB Numbers In Table With VBA Macro
Apr 30, 2014
I have some excel sheets that are formatted like the following:
COMPANY | TOTAL | R | G | B
company1 | 10 | 255 | 000 | 000
company2 | 20 | 000 | 255 | 000
company3 | 30 | 000 | 000 | 255
...
and so on...
My question is that I would like to have a macro that runs on this basic file and creates a bar graph with the data. Then it utilizes the RGB values in the columns to change the specific bar for that row. So setting the r, g, b as variables corresponding to the columns in the sheet. Also there isn't a preset number of rows in the files.
[URL]
View 13 Replies
ADVERTISEMENT
Sep 18, 2008
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
View 9 Replies
View Related
Dec 6, 2006
I have to make a macheine cycle chart. I have a column containing Step Names and a column containing step time in whole seconds. I need to paste the step names across Row 1 and whole numbers counting to X down colum A. I then need to have each second represented by coloring the cell, for example, Step 1 is 1 sec. and Step 2 is 2 sec. if "Step 1" is in B1 I need B2 colored then for "Step 2" in C1 i need C3-4 highlighted and so on for a variable number of steps. This is my first question so I hope I was clear enough. With this much programed I will be able to make simple changes to tweak it to fit my needs.
View 2 Replies
View Related
Oct 4, 2006
How can i link colors used in charts to companies i have i my database. Now excel provides colors in a certain sequence but this is not what i want. I attached a file in which i for example wants to create pie charts of the company's sales. What do i have to do to give each company its own color?
View 2 Replies
View Related
Jan 18, 2008
I want to conditionally format the fill colour of pie or donut segments based on either the value of the segment (e.g. red for negative, green for positive), or based on the value of related cells (e.g. Equal sized segments coloured red for related %age value >40, amber for 40 to 60, green >60).
View 5 Replies
View Related
Oct 30, 2006
I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series
So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?
View 3 Replies
View Related
Feb 25, 2014
When I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.
Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?
I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.
and so on.
View 2 Replies
View Related
Oct 26, 2009
I have a word, for example ROCK. I asigned numbers to the word. R=1,O=2,C=3,K=4. My Question: If I type in ROC it must return 123. If I type in KR it must return 41, etc. Is there a basic formula I can use to solve this?
View 4 Replies
View Related
Jan 14, 2010
I need a macro/script that will allow me to move rows based on cell values, i.e.: ....
View 14 Replies
View Related
Jan 22, 2009
I have a dynamic list of items which I have to assign serial numbers to.
If the serial numbers go in sequence, it would have been easy.
But the problem is, they don't.
They go from A1 - A9, B1 - B10, C1 - C10 ....
Is there a way I can assign an array to ensure that once the macro hits A9, it starts from B1, and once it hits B10, start from C1?
View 9 Replies
View Related
Jun 27, 2013
So I have
BI Raw:..... 500.....480.....570.....540.....480
TI Raw:........88.......80.......51.......22.....122
BI Rank:........3.........4.........1.........2........5
TI Rank:........2..........3........4.........5........1
The Raw rows are calculated then each given a rank. the highest is 1.
In case of a tie in the BI Raw then the lower TI Raw wins.
Also there can be up to 15 numbers in the row
So how would i assign the Rank either using a cell formula or VBA code?
View 2 Replies
View Related
Aug 12, 2014
I need to assign headers to a table according to the file name and location. I have attached the example spreadsheet. Sheet1 contains the table with the data and Sheet2 assigns the headers to each file and location. The code is skipping headers and I can not figure it out.
Macro Example.xlsm
View 4 Replies
View Related
Jul 20, 2007
I have a formula which assigns a points score to letters in a range and adds them up. In the example below F=0, P=6, M=12 & D=18. =IF( COUNTIF(AT5:BE5,"="""),"",SUM(COUNTIF(AT5:BE5,"=F")*0,COUNTIF(AT5:BE5,"=P")*6,COUNTIF(AT5:BE5,"=M")*12,COUNTIF(AT5:BE5,"=D")*18)). Thus if F, P, M & D were in cells A1:D1 the result would be 0+6+12+18=36.
My aim is to be able to customise the values of F, P, M & D, using a table and a cell value. See the table below, where the first number in each row represents a cell value which the user enters into BH1, the second, third, fourth and fifth numbers represents the values assigned to the letters F, P, M & D.
10 0 1 2 3
30 0 3 6 9
60 0 6 12 18
90 0 9 18 27
120 0 12 24 36
Some examples of expected output: user enters 10 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+1+2+3=6. user enters 90 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+9+18+27=54.
View 4 Replies
View Related
Jul 7, 2013
My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.
I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.
View 12 Replies
View Related
Mar 13, 2014
I have a column (A) with names, Column (B) with platoon numbers. On another sheet I have a table for each platoon and I want it to be populated with the names of each platoon indevidually. The names are alphabetically and the platoon numbers are mixed.
Column A Column B
Name Platoon
J Goodman 1
L Barns 7
H Law 1
B Aims 10
and so on....
I want all the names from platoon 1 to appear in the table for platoon 1. I have about 3000 members and 40 platoons.
View 3 Replies
View Related
Jan 27, 2010
Macro to export and import a module and assigning different macro automatically ...
View 9 Replies
View Related
Jan 31, 2014
I'm trying to write a macro that filters a table via textbox (criteria), specifying the column to filter through a combobox. I managed to get it to work with every format (date, text, etc.) except with numbers. I'm attaching the file so you can take a look at the code.
If I have a column containing numbers but the cells have text format and I reformat those cells to numbers (using points to separate thousands [I'm from Venezuela, we use dots, not commas]) the results aren't visible unless I modify each cell individually. How can I avoid this?
Lastly, I'm using a macro that I found online that sets invisible shapes on each cell of the header and asigns another macro to these shapes to sort by ascending or descending order in the column over which the shape is put. I made some changes to the macro that actually sorts the values and it works fine, but sometimes I have to resize the shape (on the left side) so that it's further inside the cell or else I'll get an error.
EDIT: It doesn't work with dates either!
EDIT2: I tried copying the table and the codes to a new workbook and now magically it works with numbers, but still not working for dates. Also I'm still having to resize the invisible shape (only in header of the first column ('C')) and the changes in format still aren't visible unless I modify each cell. I think this last issue has something to do with 'SortOneTime' macro or the 'Ordenar' macro because it happens after I run them.
Tabla General (Nueva) (Combobox).xlsm
View 1 Replies
View Related
Feb 12, 2014
This is probably very simple but I'm having a problem finding how to change the color of a bar based on the value. For example, when the bar reaches 100% I want the bar to turn green while the other bars in the graph stay the same.
View 2 Replies
View Related
Dec 9, 2011
I need to change the colors of each tab based on the cell value in G132.
1>Green
1
View 3 Replies
View Related
Dec 2, 2013
So I have a bar chart with numbers as its data labels...so each bar is divided into 4 colors equaling to 100% ...how can i show percentages next to numbers in the chart...so for example if a bar equals to 200 and each color is lets say 20, 60, 40, 80 ...i would like to show these numbers as well as percentages..i know we can do this manually by inserting a text box but the percentages might change quite often and i dont want to go through the hassle of calculating and changing them each time.
View 1 Replies
View Related
Aug 10, 2014
Refer to attached file.
I have below code which successfully create a macro button and assign the macro correctly.
This is only doing for one sheet and i need to modify the code so that it does for all sheets of the workbook.
[Code] ....
Test Macro_Botton.xlsm
View 3 Replies
View Related
Jan 30, 2014
Cell A1: can contain any number between 1 and 4 (with two decimal places, i.e. 2.36 or 3.99)
Cell A2: based on what number is in A1, this field should return corresponding percentage (%), based on the following criteria:
1: is 100%
2.99: 1%
3 (including) and less (all the way to 4): 0%
Note: percentages between values 1 and 2.99 are equally spread
So let's say, when:
A1 is 1.00, then A2 should return 100%
A1 is 3.26, then A2 should return 0%
A1: is 1.25, then A2 should return 92% (or whatever percentage is correct based on above described criteria)
View 3 Replies
View Related
Jun 16, 2008
i'm trying to do is have a formula that when certain text is typed into column 'b' (fmc, nmcmc, etc... anyways..) when an airplane is 'fmc' its 'green' and i want the aircrafts tail number block in column 'a' to autofill in with a green color, and when its 'nmcmc, i want it to be red. when its 'bq' i want it to autofill in as yellow.
View 10 Replies
View Related
Apr 5, 2007
i'd like to use a macro to loop through a range of cells and change the interior color index based on the cell values (ex. if cell value < 10, set interior to blue, if cell value > 400, set to green) i'm trying a for each loop with an if statement but i keep getting syntax errors, i know this is probably a simple problem but i'm not experienced in vb,
View 4 Replies
View Related
Sep 1, 2013
Can you assign a macro to the tab key?
View 4 Replies
View Related
Jul 9, 2014
excel data table within the charts. I would like to customize the data table placing underneath the chart so I can highlight (say, in red) some of the figures that are greater than certain values for easy reference. Is there anyway I can do with it (since I got more than 800 charts) than that I can only use tools to draw a rectangle and cover that particluar cells up? or is there any ways I can link up the conditional formatting of the data itself with the chart so I can applying certain coloring on the data and reflect on the data table?
View 3 Replies
View Related
Mar 31, 2014
*I have my document ordered hierarchically with group functions.
*Aesthetically I would like to have the font size get smaller the more levels into grouping they are.
*For aesthetic reasons I have merged cells rendering auto-fit useless as it auto-adjust to the first column's font size even if it has no characters in it
I find myself contemplating manually adjusting all 500 some rows of data.. probably by selecting ranges with ctrl or shift clicking my way through the document, grabbing ranges based on how high their grouping is, and adjusting the whole selected group at once, so they all have a uniform height setting.
Can I do some kind of conditional formating based on group level? like all group level 7 rows have a row height of X.
View 1 Replies
View Related
Dec 13, 2013
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
1002856101
22781721
1044863815
1008799064
1044779765
1006511115
1007641804
0729939256
5303486020
8364709
0014094759
0019921519
8172717
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------
=IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's.
-----------------------------
View 3 Replies
View Related
Sep 28, 2013
I have a data base in which I classify cells in three colors (green, yellow, and red) on my own. I would like the first cell in each column to indicate if there is a red or yellow cell in it's column. Because red cells are more important then yellow cells, if there is a yellow and a red cell in the column, I would like the red to over ride the yellow and the first cell would indicate red.
So basically, if there is a red cell in the column, no matter what other colors there are, the first cell should show red. If there is only yellow or yellow and green cells, the first cell should show yellow. If there are only green cells or green cells and clear, the first cell should show clear.
View 1 Replies
View Related
May 8, 2008
I have a spreadsheet that I enter monthly expenditure on.
Column A is expenditure during 24th to 31st
Column D is expenditure during 1st to 8th
Column G is expenditure during 9th to 16th and
Column J is expenditure during 17th to 23rd
Ive been trying to colour the columns grey if todays date is outside the above date ranges each time I open the spreadsheet so its obvious which column my expenditure needs to be entered into.
View 3 Replies
View Related
