Conditional Formatting Of Bubble Chart Points Based On Bubblesizes
Jul 26, 2012
I am trying to format the colors of the bubbles on my bubble chart to Green (>5), Yellow (4-5), or Red (<4) based on the value used to create the bubblesize.
The problem that I am having is that .BubbleSizes is property of type String, not range like .xValues, or .Values. So instead of pulling in the value, I am pulling in the reference and getting a "Type Mismatch" Error. The reference of my data for .BubbleSizes is $D$5:$D51. Each row is it's own series and the list is dynamic, so I need to be able to support future rows without a lot of maintenance.
Here is the code that I have so far.
VB:
Sub DataSeriesFormat()
'
' DataSeriesFormat Macro
' Format Data Series based on defined parameters for Green, Yellow, and Red. Created by Derek Steinmetz 7/24/12
'
Dim x As Integer
Dim val As Variant
Dim Green As Range
[Code] ......
View 1 Replies
ADVERTISEMENT
Nov 26, 2013
I have the table: chart1.png
If the bubble size is 0-5, I need the bubble color to be green
If the bubble size is 6-15, I need the bubble color to be yellow
If the bubble size is 16-25, I need the bubble color to be red.
So, right now my chart looks like this: chart.png
As you can see the top bubble is the right color, but based on the bottom bubble value, it should be green.
How do I make this change colors dynamically based on the bubble size value?
View 1 Replies
View Related
Aug 2, 2013
I created a bubble chart whose x-axis and y-axis show every 10th value between 0 and 100 (i.e. 0, 10, 20, 30...). The axes intersect at (50, 50) to create 4 quadrants.
I want to erase/hide the values on the axes (i.e. the axes will no longer show 0, 10, 20, 30... and at the same time, the bubbles will still be in the same place) to create 4 blank quadrants and make my bubble chart easier to look at.
View 4 Replies
View Related
Aug 30, 2008
Let Sheet 1 have data in 3 columns to support a standard bubble chart
Col A: X Values
Col B: Y Values
Col C: Bubble Size
Id like to be able to color the bubbles according to the Y values, whether the Y values fall within the following ranges
Green for Y <=2
Orange for 2<Y<=5
Red for Y > 5
Im assuming some straightforward VB code is in the works but Im not quite there yet
View 4 Replies
View Related
Sep 11, 2012
I'm trying to write a macro that will format my chart data based on the corresponding cell value. I have the following code, which runs without error, but the formatting on data points doesn't change. I can't seem to get this to actually change the color of the data point in the chart.
Code:
Sub FormatChart()
Dim wb As Workbook
Dim ws As Worksheet
Dim wks As Worksheet
Dim y As Long
Dim j As Long
'this routine will format the charts on worksheets "SL11730" & "SL14100" 'so that data points equal to or above 98% will be green filled and 'all other points will be red filled
'set loop counter to two (contract (4100) & retail (1730))
j = 1
'start loop
Do Until j = 3
If j = 1 Then strwkb = "SL11730"
If j = 2 Then strwkb = "SL14100"
[code].....
View 4 Replies
View Related
May 24, 2005
I am trying to format the colour of a column chart based on the value of the x axis (not the y axis value). Firstly I need to know if it is possible. Secondly I would appreciate any hints / VBA code that could be useful.
View 4 Replies
View Related
May 16, 2008
Background: I have a Chart sheet with a combination bar/line chart on it. Data is coming from a "datasheet". There is a combo box on the chart sheet that allows you to choose a specific office whose data you want to view. Pick an office from the dropdown and the chart changes. The data is a count of closed cases by month. The data sheet shows 22 Offices with their monthly data, along with entries for each month of the Upper Control Point, Lower Control Point and Mean based on the nationwide data for that month.
The chart itself uses bars to show the office's case count per month, and I've incorporated 3 additional data series to plot the UCP, LCP and Mean as lines for each month.
I'd like to change the color of the bars to be Blue, Green or Red depending on where they fall in the 3 lines. I sort of have it working, but I'm stuck.
The code below grabs the value of each series point and compares it to the 3 control points and does change the colors. I've figured out how to use the If, next to change from one series point to the next, but cannot figure out how to loop through each of the control points. As it stands now, it looks only at the first month's control point and uses that for each successive month.
I hope this is clear and I've tried to keep it as short as possible while still providing enough information.
Sub chartbarformat()
'
' chartbarformat Macro
' Macro recorded 5/15/2008 by vacoyoungd
'
Dim x As Integer
Dim val As Variant
Dim AveragePending As Range
Dim LowerPending As Range
Dim UpperPending As Range
View 8 Replies
View Related
Jun 30, 2009
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.
My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
View 2 Replies
View Related
Apr 8, 2009
I have two arrows:
- Red Arrow points down to signify negative change
- Green Arrow points up to signify positive change
These arrows look exactly like the Excel 2007 conditional formatting arrows you would apply to a cell - the only difference is that I have inserted them as shapes so I can float them over a graph.
GOAL: Corresponding with the graph, if a cell shows a (+) change, then I display green arrow and hide red arrow. Vice versa for a (-) change.
View 7 Replies
View Related
Jun 28, 2009
I would like to combine / overlay a bubble chart and scatter chart with straight connectors.
I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart.
Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment).
My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?
View 2 Replies
View Related
Jul 13, 2007
Conditional formatting is lacking. I need to colorize each data point on the graph according to a characteristic cell (not an x-axis or y-axis variable). I have done something similar by simply graphing a group of points as a series and changing the series color. I'm looking for a more graceful solution. From the example picture attached (i would attach a workbook, but the data is sensitive), a point is charted based on two variables and colorization is attempted based on the third variable. i would find the range of the third value, assign a gradient based on the spread, and colorize each point. My VBA syntax is lacking for chart functions
View 7 Replies
View Related
Sep 27, 2007
create a Quadrant Bubble chart. I have found examples of using scatter charts but unfortantely those charts do not satisfy what I am trying to accomplish.
View 5 Replies
View Related
Apr 10, 2012
I have the Lat and Long for each of several US cities. I want to use these to create a bubble chart that plots the cities and which I can place over a US map picture.
However, I have the problem that, because of the curvature of the earth, the city locations form the lat and longs are not tying with the map.
how to adjust the lat and longs so that they will correspond to the typical US map?
For example, the base lat and longs for Denver are 39.5742 and -104.8588. What formulas can I use to modify these to match the standard map projection?
View 9 Replies
View Related
Apr 15, 2007
I’m trying to take an existing bubble chart, paste new data into the sheet,
then march sequentially from row to row, adding specific cells within each as a new series in the chart until I run out of rows. Below is my latest attempt (not working of course).
Sub setseries1()
Range("A1").Select 'select upper left cell to start
Do Until ActiveCell.Value = "" 'stop when you run out of rows
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlBubble
With ActiveChart.SeriesCollection.NewSeries
'use these values for the next series
.Name = ActiveCell.Offset(1, 0).Select
.BubbleSizes = ActiveCell.Offset(0, 1).Select
.Values = ActiveCell.Offset(0, 1).Select
.XValues = ActiveCell.Offset(0, 9).Select
End With
'go to next row, and repeat
ActiveCell = ActiveCell.Offset(0, -1)
Loop
End Sub
View 7 Replies
View Related
Jul 12, 2007
I have a worksheet where I am collecting data and a seperat Chart. The first part of this code which adds a new row in my table works perfectly fine, but the latter part (based on a macro recording) does not. I need to specify the relevant row for the new series in the bubble chart. The colums are of course the same.
Private Sub CommandButton1_Click()
Dim LastRow, LastRef As Long
'Works!
ThisWorkbook.Worksheets("Projektradar - input").Select
Range("Q3").Activate
LastRef = ActiveCell.Value
Range("Q3").Value = LastRef + 1
View 4 Replies
View Related
Dec 19, 2006
When you hold your mouse over a bubble on a bubble chart, it typically displays the x value, y value and size value. Is there any way to add other captions to display? For example, if my chart is based on sales data and I'm plotting a point based on ease of probability (x axis) and dollar value (y value)....I'd also like to see the initials of the salesperson assigned to that prospect when I hover over the bubble?
View 2 Replies
View Related
Feb 16, 2007
I am looking to create a dynamic bubble chart. To do this I am using offset to create the series formula which without listing all the data names turns out to be something like this (where 297 will adjust to the number of rows with data): =SERIES(Project!$A$5:$A$297, Project!$J$5:$J$297,Project!$P$5:$P$297,2,Project!$Q$5:$Q$297)
My problem is that by creating the chart in this fassion I am unable to get a unique name for each data point. For example, if row A looks like this,
A5: 1
A6: 2
A7: 3
each data point will be named 1 2 3. How do I either create a new series for each row dynamically or get the specific name from column A to associate with the correct data point?
View 2 Replies
View Related
Apr 4, 2014
I have a number of bar charts for which I'd like to colour the bars blue if the associated value is greater than zero and red if less than zero.
Is it possible to get Excel to assign the colours accordingly, in the same way as it does with conditional formatting in worksheets?
View 3 Replies
View Related
Dec 4, 2012
I have a Pivot Chart with 5 data series requiring 3 different formats.
Data: In my Pivot Table I have the following Fields - Type, Year, Month, Sales.
The Years are always the most Recent 2 Years (Currently 2011,2012).
The Months are, well, the Months Jan Through Dec.
The Sales are 'Number of Sales in The Period'
The Type is one of 3: Sales, Cumulative Sales and Change.
Sales has a field for every month in 2011 and 2012, the same for cumulative and finally, Change has the difference between 2011 and 2012 sales for each month.
Now when I plot these on the chart I have
Sales 2011, Sales 2012 Plotted as a line chart on the primary axis,
Change Plotted as a bar chart on the primary axis, with invert if negative formatting,
Cumulative Sales 2011, Cumulative Sales 2012, Plotted as a line chart on the Secondary axis. With the same colours per year as Sales, but as a Dashed Line.
I want this formatting to remain static as the data updates, and if possible when the series changes name (which i'm not sure is possible) because, come January the data series will be 2013/2012 not 2012/2011.
With the other formatting is seems to sometimes stay static and sometimes just decides to reformat everything and switch axes and i'm not really sure why.
View 1 Replies
View Related
Dec 20, 2011
Is it possible to aply conditional formating in Chart. for eg. my data in is below format
MonthNo of PO'sNo of GRN'sJan23444443Feb34343453Mar45425566April19002123Average PO's3055Average SO's3896
Now I want a Coloumn chart in which line changed to Red color which are below average. Currently i have to do manually change in it. Is it possible to change column chart color as per condition.
View 5 Replies
View Related
May 23, 2014
I am new to excel and to the bubble chart function and need creating one for a presentation.
I was asked to do a bubble chart to show the open and click to open percentage by subject line
E.g.
Subject line: Win 20% open, 23 % Click to Open, Getaway 20$ Open, 21% CTO etc., to include an industry average for open and CTO in the chart.
X axis: click to open and Y axis: open
I tried the following in the picture and it doesn't look right.
Capture.JPG
View 1 Replies
View Related
Nov 9, 2006
Im trying to create a simple 3D Bubble Chart macro, and as most people who've never dealt with this before (i think), i've hit a wall with BubbleSizes. I've attached the file im trying to get to work. The VBA is quite simple but an error occurs at BubbleSizes. I've read that BubbleSizes takes a different reference style than xValues and such, but haven't had any luck trying to change that. For those who just want to see the code without getting the file:
Sub EmbeddedChartFromScratch()
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart................................
View 5 Replies
View Related
Dec 14, 2009
Hey Guys- I'm not sure if this is even possible but I need some help. I have attached a file below similar to a gantt chart. I need help with the conditional formatting, as I have manually changed the colors to match what I need automated.
Basically I have 6 tasks and I need to change dates for individual projects but I also need the corresponding color to also change.
View 9 Replies
View Related
Jun 11, 2014
I currently have a thermometer chart in Excel 2013 that I set up with percentages along the Y-Axis. The thermometers are showing spending based on a budgeted number. Currently, the project is over-budget so I have adjusted the thermometer scale to go far beyond 100%.
I would like to have the percentages past 100% show in red along the lefthand side.
View 2 Replies
View Related
Jan 8, 2014
I have a horizontal bar chart in which some of the bars represent positive values and others represent negative values.
How to format the bars so the "positive" bars are shaded in one colour and "negative" bars in another colour? I'd prefer not to change the bar colour manually as the values change frequently.
View 2 Replies
View Related
Dec 2, 2012
I have 60 days of data, Nov 1st through Dec 31st.
The date is my x-axis labels.
I produce a daily report. I'd always like for the current dates vertical bar to be "red".
Is there any easy way to have this accomplished?
View 6 Replies
View Related
Jun 16, 2014
I am looking for the best way to use conditional formatting in a Gantt chart. I want the cells to turn blue if they fall between two dates.
View 4 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Feb 2, 2012
I currently have a spreedsheet which has a conditional formatting cell where if the figure is above 1 is green and below red what i am trying to do is get a sum to calculate based in its color?
I.E. I have a value in cells F7+F25+F27+F29 i then have a value in F17 but only want this to be included in the total calcualtion if the figure in E15 is equal/above the value of 1.
View 3 Replies
View Related
Jul 27, 2012
I use data in column A:B to draw stacked line chart . My question: I want to add column C Evaluation on every point on stacked line chart.
Sheet1
*
A
B
C
1
*
Sales
evaluation
[Code] ...
View 4 Replies
View Related
