Does any one have VBA code that draws a heat map or color map based on input data. I would like to vary colors & size of the boxes based on input variables. Something similar to this example would be neat -- [url]
View 9 RepliesTrying to find the Row(Window) and Column(Percentile) of the highest value in the grid. The value in Cell:AF7 finds the value in the grid(Heat map).
Once we calculate the Max value in the grid. We set the number of data points Above/Below in Cell:AI7 (This we enter in manually).
We then go into the Cells in the grid(Heat map), and then calculate the sum of the cells above and below the maximum number (which is returned in Cell AF7) dependant on the number of Data points Above/Below we are looking for. An example of this is done in Column D to M Rows 51 to 61. The sums are calculate of the cells above and below our maximum value in the grid.
We then want to return the maximum value of the sums of the data points above and below and then return the Percentile and Window Value.
As an example. Our max value on the heat map is 35.30026 and we want 2 Data points Above/Below. Then the maximum sum is 39.12358. Thus the percentile is 0.62 and the Window is 28.
I need to create many XY charts in a VBA macro. For each chart, I need to select a particular column for the X dimension, and a particular column for the Y dimension. When I use a named range to define the X and Y columns, my macro works fine for those instances when the X comes first. However, if my desired Y dimension comes first, I cannot find a way to correctly select these.
The context is such that my spreadsheet always has the same data fields in the same columns, but the numbers of rows vary. In addition, I need to dynamically select which rows to use, based on the values in the columns to be plotted. I’ve figured out how to do this within the macro, except for those instances when the Y column comes before the X. An example when this works:
Sheets("Raw").Select
Names.Add Name:="newRange", RefersTo:="=$K13" & ":" & "$K" & FirstBias _
& ",$M13" & ":" & "$M" & FirstBias, Visible:=True Range("K1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Raw").Range("newRange"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Field10vsField12"
With ActiveChart
.HasTitle = True........................
I am trying to create a Heat Map for Risk in a spreadsheet that auto populates. Very simple dataset and I can convert my values to numeric if that makes the graph easier. Data Set is basically as follows:
Column 1 - Risk Description
Column 2 - Impact (score of 1-5)
Column 3 - Probability (score of 1 to 5)
Column 4 - Risk Indicator (Col 2 x Col 3 to give score out of 25.
Looking to create Heat map with upper right being my highest risk and lower left the improbables or low impact. I should mention that my VBA is as good as my Latin skills, i.e. Zero! so looking for a way of doing it through excel, either suggested or inbuilt charts.
I'm trying to create UK County heat map, however I can't seem to find example of one I could edit.
View 1 Replies View RelatedI am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
T. A1 B1
Sample 1 1.2
Sample 2 2.1
Sample 3 1.7
Sample 4 5.6
I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.
View 3 Replies View RelatedI have got a userform with lots of controls,
One of the action's on a large group of the controls is the same but except for one number
here is an example
If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3
This makes a cell that correlates to the textbox red if the text in the textbox is red.
Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.
Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?
I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine
Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.
I 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'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies View Relatedhow I can alternate the background color (fill color) of rows in a spreadsheet. Say I wanted every other row to be gray starting at row 10.
View 6 Replies View RelatedIn my Excel 2003 worksheet, I need the row color to automatically change to blue (color 5) (bgcolor = #0000FF) - when the user changes the text from VALID to INVALID in the range: B3:B65000.
For example:
Cell B5 contains the text: VALID
When the user changes the text in the field to read: INVALID - then I need the row range: A5:W5 to change to the color blue.
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.
I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.
The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.
What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.
Here is the code I am using to set the color of the cells in Column A:
I would like to be able to change the color of a cell in V4:AB31 and have the formula in AM10:AM13 automatically calculate the new result. As it is now the user has to press Ctrl ALT f9 for the formula to recalculate.
View 7 Replies View RelatedI have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View 3 Replies View RelatedFor some reason the fill color and font color no longer work! If I set the font color as red, it continues to show up as black!
This is the strangest thing! I have no conditional formatting set and this just started a week ago.
I have font color white in blank cells in column E and I (from row 5 to row 245) so the visitors will not see the text. If any of these cells become yellow (color code is 6), the font color will become black so visitors can see the text alot far better than white. I've tried this code myself after this post but nothing happen
View 4 Replies View Related1. By entering a numerical value in Cell A1 for example typing 5.
2. Then, next five columns would be blocked with specific color.
3. if i am typing 10, then 10 columns would be blocked with specific color.
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.
i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?
if it actually possible to colour data points depending on the colour of the cell of the data it refers to? if not is it possible to colour it any other way
i have uneven amounts of data for weach month but still want the months to be displayed, this is the best way i can think of of doing this
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 want every even row that has a value in it to have a different fill color (same color for all). how to do it? I am using 2007.
View 5 Replies View RelatedI've reached a problem with colors in Excel: need to convert cell color (get from .Interior.ColorIndex property) into RGB value (to set ForeColor of the button). How can I make it?
View 8 Replies View RelatedI want to be able to link the colors of 8 differnet TABS of individual worksheets that have each been colored differently to the colors of individual columns of a chart - being a summary chart of information in another worksheet that has come from seperate worksheets within the same workbook. So that if someone was to change the color of any tab then the color of the corrosponding color of a column in the chart would change automatically if the color of a TAB is changed.
View 9 Replies View RelatedThere must be a VBA command out there, with which one can call the color panel such that the user can then choose his/her preferred color, I just cannot find it. Could you help me out?
With Color-Panel I mean this little interface you get when manually changing the font-color or the fill-color. (it consits out of maybe 8 colors in a row and 5 colors per column and usually has a button "automatic"). I would like to call that interface such that it gives back the color-index of the color choosen by the user.
I would like to have cells that conditionally format the font color in the cell based on the background color of the cell. Essentially I have a matrix with some cells highlighted in red, yellow, or orange. There are values in each cell of the matrix. If a value is 0 I want the font to be grey. If the value is NOT 0 AND the cell backgorund color is NOT white, I would like it black and bold. Is there any way to do this in excel or using VB?
View 6 Replies View RelatedIn excel 2000, I am trying to count the lines in a spreadsheet that has the first cell in each row highlighed in yellow. Additionally, I am trying to count the number of rows that has a red font used in the cell in column D.
I have reviewed other posts by searching counting highlighted cells, but I do not understand how to complete the entire process. One of the suggestion was to create a vb script in combination with using =cellindex(??). I do not really understand how to create this, name it and the run it.
I have a column of student grades & accumulated credits.
1.) I would like to apply a CF formula which turns the cell GREEN when a score is above 65 else RED below 65.
2.) Also I would like to turn another cell GREEN if a student has accumulated the required # of credits listed in another cell else RED.