I have a dynamic chart which I would like to show markers in a SeriesCollection anywhere the backcolor of any of the corresponding source cells is ColorIndex = 36; otherwise no marker is shown. Something along the lines of:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred
when copy/pasting, but IS if the keyword uses "quotes".
With Sheets("Chart").ChartObjects(1).Chart.SeriesCollection(1)
For i = 1 To UBound(.Values)
If "something here to check if .Interior.ColorIndex = 36" Then
.Points(i).MarkerStyle = xlMarkerStyleCircle
Else
.Points(i).MarkerStyle = xlMarkerStyleNone
End If
Next i
End With
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2) these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
to create a chart which shows a current value in relation to it's annual high and annual low. I've created a bar chart and added a series with a value of 100. The next series I added is the value of the marker I would like to ride along the bar.
My question is how do I change the marker value series from a bar to just a marker? I would like the chart to look like this (except a bar, not a line): __________
I'm trying to use an image as a custom marker on an XY scatter chart. The jpg image is embedded on a separate worksheet and when I copy and paste it via code it seems to be arbitrarily resized. I've also tried to resize the embedded image and set the marker size but it always appears about 3 times the size of the original image. See CASE -0.5 below.
Set seriespoint = chtBaseArea.SeriesCollection(" Events").Points
For i = 1 To rngCombPointsY.Rows.Count With seriespoint(i) .HasDataLabel = True .ApplyDataLabels Type:=xlDataLabelsShowLabel .DataLabel.Text = rngCombPoints. Cells(i, 1).Value .DataLabel.Position = xlLabelPositionAbove .DataLabel.Font.Bold = True .DataLabel.Font.Size = 8
Select Case rngVehicleY.Cells(i, 1).Value
Case -0.5 Set shpTemp = wrkshtGraphics.Shapes("Soyuz_Small_Icon") shpTemp.Copy .Paste .MarkerSize = 10
I have a simple Excel 2007 column chart which has names listed along the y axis for each column. Associated with each name in a small table is a colour reference. Is it possible to add a small marker (say a triangle or something) that aligns with each column?
Or possibly (or in addition) colour each column differently and in accordance with the assigned colours? I'm doing this with some vba code. but if someone can point out the general principle I should be able to work that in.
I'm trying to get the bars of a chart to change colour depending on the selection from a drop down in a cell. The drop down is validated to be one of four options which changes the figures displayed in the graph using index matching.I would really like the colour to change to represent what the option being selected is.
I used some VBA code before for changing the cell colour depending on the selection made to get over the 3 condition limit. Could this be easily edited to change a bar graph colour? All of the references I can see on line refer to changing the colours of bars based on their value, I don't want to do this, more an independant cell value!
My previously used code is below:
Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Integer
If Not Intersect(Target, Range("d9:d129")) Is Nothing Then Select Case Target.Cells(1)
Case "Commercial Final State" iColor = 49 Case "Commercial Interim" iColor = 31
I have inherited a spreadsheet from someone else that rather cleverly (in my humble opinion) sets the colour dependant on the contents. ie negative value is red, zero is amber and positive is green.
i like the idea and would like to use it on a spreadsheet of my own, but have tried to find out how it is done and can't!
I feel a bit silly asking this in the presence of so many obvious excel gurus: but here it goes. I've created some user forms, activex controls, etc. that do a series of complex calculations, supported by VBA and it's at the point where I want to roll it out. How do I hide / get rid of the active cell marker on the sheets? I've looked in the "options - view" section and have lucked out.
I have a general list from which a chart is created. Column one contains a date series. There are a fixed number of dates in this series (approx 50 or so).
I would like to select a start date and an end date from this list and have the chart display the data from that range.
Typically I would simply create a new list and build the chart from the list, but I'm kind of hoping that I could do it from a single source rather than make mulitple lists.
I can't figure out how to use VBA turn off autoscalefont in a chart textbox. .. (this is a textbox I have created in a chart using the shapes.addtextbox method..
I have created a pseudo Gantt chart in Excel (a horizontal stacked bar chart). The chart is driven off data I have in a Power Pivot model (Auto Refreshed) and the Gantt chart displays data which spans a fixed 12 hour time frame.
The problem I am having is that in order for me to get the chart to display correctly I must manually (daily) get the excel decimal values for my Minimum DateTime and Maximum DateTime and then enter those numbers into the "AXIS Options" Bounds Minimum and Maximum value fields. I would like to automate that process so that the Axis Bounds are either driven from the data cells OR are always set to a specific 12 hour window 6pm->6am daily.
I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.
However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .
VB: 'activate sheet and chart Worksheets("Home Page").Activate ActiveSheet.ChartObjects("Histogram").Activate
I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
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've got two sheet's in a workbook. The first contains data and is setup with dates in column A, day of the week in column B, and values going across by day in column's C through H. As an example it looks as follows:
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
Y3:Y500 contains a range of years, 2014/2015 through to 2018/2019, for a total of 5 different values (So conditional formatting is out the window). I'd like code which highlights the rest of the row, Z:FN, in a certain colour depending on the value in Y3:Y500.
Also, where there is data in the cells between Z:FN I'd like that to highlight in yet another contrasting colour.
I'm trying to create a simplified Gantt chart of sorts, and cannot figure out one piece of it. I'm not sure how to write out exactly what I need, but here goes.
I would like to have a formula that looks at the percent completed (which the user inputs) and multiplies it by the total duration for the task to give a total number of days completed. Then, under the corresponding dates, the color of the cell would change. I've attached a simplified version of what I'm talking about.
On the attached spreadsheet, the total duration (D2) is 5 days and the percent completed (C2) is 40%. This calculates to 2 days. With that said, I would like the cells under the first 2 days under the listed dates (E2 and F2) to change color. When the percentage complete reaches 60%, then G2 would change color, when it reaches 80%, then H2 would change, and when it reaches 100%, then I2 would change.
My guess is that the formula will need to result in a particular value, and then I'll use Conditional Formatting to do the actual changing of the cell color. Assuming this is correct, I still don't know what formula to use to accomplish this.
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
A line graph is using a data row to plot the line; works fine. The data series ranges have been named for Week1 (H10), Week1_2 (H10:I10), Week1_3 (H10:J10), Week1_4 (H10:K10), Week1_5 (H10:L10), Week1_6 (H10:M10), Week1_7 (H10:N10), ...etc.
This is the data series formula name example to plot the data series from week 1 through week 7; ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7.
The series name formula Q3Wk1_7 for week 1 through week 7 is =$H$10:$N$10.
When this name is manually entered as a Series Value for the line graph, the graph plots exactly the way it is expected. The same is true if the Series Value is manually modified for some other ranges such as Week 1 through Week 9 (='Product Plan Q3 Week 7.xlsx'!Q3Wk1_9).
My challenge is to enter this Series Value based on the manually entered week number elsewhere; i.e., we're in week 7, the Series Value should be ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7. I've been trying to use an HLOOKUP table to select the correct Series Value formula based on the week number. The HLOOKUP works fine resulting in selecting the correct formula. I can also Name the range of data I want to graph and copy the name into the Series Value field with successful results. How do I get such a formula or Named range into the Series Value field without type it in? I want to set that Series Value to a range or name without having to select the line to manually enter the value.
