Using XL07 I'm trying to programmatically set the fill type to solid and the colour to a colorindex constant for a series in a chart.
I can select/activate specific charts and series, but can't work out how to modify the properties of the series.
I can't for the life of me piece together the method, properties and syntax for doing this.
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....
View 9 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'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 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.
1. 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.
Bet this is simple to someone...
How can a continue this fill series down a column?
=$BC$3
=$BC$4
=$BC$5
=$BC$6
I would copy, paste and edit only there are 2500 rows!
I want to run a series of cells but how to do it. E.g.
Cell A1 to contain the data 2A
the next cell (A2) to read 2B
the next 2C etc etc
I've tried just dragging the cells down but it just duplicates 2A, 2B, 2C whereas I want it to fill the next cells as 2D, 2E etc
And I've tried using the fill series option but neither seems to be working (presumably due to the letter).
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 am working on a spreadsheet and I need values from Sheet1!a2, a7, a12, a17 etc...
I want to fill in the formula ='Sheet1'!a2 etc, to fill column A in sheet2!
When I highlight and drop down to fill excel fills the column in increments of five like i need but doesn't start on the right one. Instead it fills the formula as ='Sheet1'!a6, (For cel A1) and ='Sheet1'!a11, (for cell A2) and so on.
I am not able to drag & fill series only in one particular sheet, in other sheets this function is working fine.
View 2 Replies View RelatedI would like to use the Fill Handle to create a column of dates.
The dates must be formatted like this: DD/MM/YYYY HH:MM
The Fill Handle completes the series, but not the way I want.
I want a series that changes the day only. So:
02/02/2012 09:00
03/02/2012 09:00
04/02/2012 09:00
For whatever reason, Excel currently adds a minute or two to the minutes and does not increase the date.
13/2/2012 09:20
14/2/2012 09:20
15/2/2012 09:20
13/2/2012 09:21
[Code] .......
I am trying to use the auto fill series but I need the series to start over every time the value change in another column.
If i use the auto fill series, it will copy 1,2,3,4,5 and so forth til the end of the column. In this case, i need the auto fill or some other function that will restart once it encounters a different value under the ID column. I've started below to show an example.
IDNo
0.11141
0.11142
0.11143
0.1121
0.1122
0.1123
0.1124
0.1125
[code]....
How to make fill series number with simple excel formula, like table below :
col.a (desired result)
col.b
1.
john
2.
lia
3.
leo margarette
4.
david lee
etc....
note : not use Vba...
Prior to declaring a variable i used this code and it worked:
Sheets("442000ON-1+6").Range("C7").formula = "my formula"
range(C7).select
Selection.AutoFill Destination:=Range("C7:N7"), Type:=xlFillDefault
Than i declared a variable b/c i have a number of formulas i need to use but still needed it to fill the formula in a series after the formula ran. I started getting an error.
Fix was:
Dim formula1 As String
formula1 = "=SUMIF(Pivot!$A$6:$A$108,'442000ON-1+6'!$C$3,INDEX(Pivot!$B$6:$BP$106,0,MATCH('442000ON-1+6'!$B$1,Pivot!$B$5:$BP$5)))"
Sheets("442000ON-1+6").Range("C7").formula = formula1
Range("C7").Select
Set SourceRange = Worksheets("442000ON-1+6").Range("C7")
Set fillRange = Worksheets("442000ON-1+6").Range("C7:N7")
SourceRange.AutoFill Destination:=fillRange
I have a column:
LABEL COUNT
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 16
15 empty cells
I need to fill the empty cell with the "series" (2 of 2, 2 of 16, 3 of 16). This is just an example of data - there are all different series - 1 of 12, 1 of 5, etc.
Anyway - was hoping to highlight the whole column and be able to auto fill instead of having to extend each series. I have tried straightforward fill series which doesnt work of course. I have tried go to special - blanks but cant find right combo to make it work.
Will it work or do I have to either do manually or with a macro?
I need to fill multiple number series in the same column. Example:
Have
Want
1
1
[Code]....
I have a list of 600 unique project IDs in column A. I want to create a second list in Column B that references the contents in Column A, first row then duplicates that item for two subsequent rows, then a formula in the fourth row of Column B a reference to the second project ID in Column A row 2, with two duplicates in the fifth and sixth row.
Column A
Row 1 000891.AB
Row 2 000892.CD
Need to create:
Column B
Row 1 000891.AB
Row 2 000891.AB
Row 3 000891.AB
Row 4 000892.CD
Row 5 000892.CD
Row 6 000892.CD
Auto fill series based on Condition.(Excel 2003)
I know very little about macros (basically just concepts). I found this macro suggested in another thread (posted By VoG II…Thanks)
Sub test()
Dim Lastrow As Long
Lastrow = Range("A5").Value + 4
Range("B5").AutoFill Destination:=Range("B5:B" & Lastrow), Type:=xlFillSeries
End Sub
When this macro is run the first time it will fill in the numbers 2 through 15 (as I had hoped). Given 15 is the variable and 1 is the start of the range...
I would like to find the blanks within a column. Using the available cell values above and below compute a sort of linear function then, using that linear function fill in the blanks in between. So the values are linearly increasing or decreasing (whatever the case maybe) to the next available nonblank cell value. spreadsheet attached.
View 3 Replies View Relatedi have a time series of data at one hour interval ( from 6:00 hrs to 18:00 hrs ) each day and again from 6:00 to 18:00 hrs next day and so on for several years. in this series many data are missing. i ve to fill in these missing data in such a manner so that the new values becomes the linearly interpolated values in between the data given at any two hours. no rows are allowed to be inserted in between 18:00 hrs of the previous day to 6:00 hrs on the next day. pl see the attatchment.
View 2 Replies View RelatedI have a spreadsheet where the start date is manually entered in Cell B1 and an end date is calculated in Cell B3. I would like to have the dates between the start and end date, with a step value of 7 days show in the rows under Cell B3. How do I get this to work?
View 3 Replies View Relatedregards the two attached files (both are identical) except '1' has TWO Rows of data above the Macro Button and works correctly whereas '2' has only ONE Row (the desired option but does not work correctly!( Inserts Row in wrong place and incorrectly fill series)).
basically the program inserts a additional row below the last data entered, copies a formula cell from above and finally (the problem area) Series Fills a cell from data above.
I am newer here and I want a macro in mentioned title. I have a number in column a2 and in column b2 quantity if in column a2 number is 2101400 and in column b2 i write quantity 50 so said number automatically fill series in column a like mentioned below...
View 9 Replies View RelatedI have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.
Part 1:
I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)..................
I have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:
Start Range End Range Qty
2101200 2101499 300
I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc.
but some time these numbers are in qty 30000 or more. I have questions
1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.
My Second question is opposit of my above question.
2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example.
Series [b]Start Range End Range Qty
1 1 1 1
3 3 3 1
5 5 7 3
6
7
I have a data set that I'm trying to present in a pivot chart and am wanting to use one series to color code or weight another series in the chart. For instance, let's say I have different product categories and two metrics for each, a profit margin (expressed as a %) and a revenue figure (expressed as $s). I want the height of individual bars to represent the profit margin but have the level of opaqueness represent the revenue figures...ie the more revenue associated with a product the more opaque it becomes.
View 6 Replies View RelatedI'm trying to write some code to make an existing chart display the correct series of data. However, on the ".SeriesCollection..." lines, it gives me the error "Object does not support this property or method." I'm using a Bar-Line graph, if it makes any difference.
View 5 Replies View Related