Free Microsoft Excel 2013 Quick Reference

Changing font style/size/color/etc for multiple series in a chart?

I have a stacked bar chart (but note applies to all charts) that I have added
data labels to in Excel 2007. However, the data labels are the wrong font
size and color.

When I go to format data labels in the "Labels" section of the Chart
Tools/Layout ribbon, there is no apparent option to change font. I can right
click on a single series and change it, but doing that for every series in a
complex chart seems ludicrous.

Is there any way to change the font for multiple series in one step?
The same holds true for changing the number format or any other
characteristic for multiple series at once (the format data labels still only
applies to one series or label at a time - very painful).

Thanks for any help!


I would like to create a Defined Name range to use as a the value for a
series in a chart.

Example: Cell B2 equals January Sales; Cell C2 equals February sales; and
cell D2 equals March sales. So, B22 equals sales for Jan, Feb, and Mar. I
can name that range of cells (B22) "Sales".

I would like to have a series in a chart named "Sales". Now, for the Values
of that series, I could put B22. However, I would like to put the Defined
Name "Sales" instead. That way, if I have 5 different charts that graph
monthly sales (B22), to add April sales to the charts, I don't have to go
to each chart and update the Values range to be B2:E2, I could just update my
Defined Name of "Sales" to now be B2:E2, and all of the charts should update
automatically.

I would like to create a Defined Name range to use as a the value for a
series in a chart.

Example: Cell B2 equals January Sales; Cell C2 equals February sales; and
cell D2 equals March sales. So, B2:D2 equals sales for Jan, Feb, and Mar. I
can name that range of cells (B2:D2) "Sales".

I would like to have a series in a chart named "Sales". Now, for the Values
of that series, I could put B2:D2. However, I would like to put the Defined
Name "Sales" instead. That way, if I have 5 different charts that graph
monthly sales (B2:D2), to add April sales to the charts, I don't have to go
to each chart and update the Values range to be B2:E2, I could just update my
Defined Name of "Sales" to now be B2:E2, and all of the charts should update
automatically.

I have been trying (unsuccessfully!) to write some code to change the font
style and color based on specific criteria.

I have a spreadsheet with input cells in columns A & B and a formula in
column C to determine the % variance between the 2. What I want to do is in
column D return a symbol based on a RAG status (Red, Amber, Green). I can't
do this using a formula as the Red and Green symbols are Wingdings and the
Amber symbol uses Wingdings 3.

So if A1=40 and B1=45 the variance is 11% (as shown in C1)and I'd like this
to return a particular symbol (Amber triangle), which I believe is:
Value = "Ā"
Font Name = Wingdings 3
Font Color = RGB(255,192,0)

But I'm struggling with the actual code....please don't laugh at my pathetic
attempts (completely self-taught) but this is what I wrote....

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("G6")

Hey again everyone. I'm working with a huge database now that includes in each row one column that lists phone numbers. I want to be able to highlight when multiple rows have the same phone number. I can sort them so that they'll be assembled together on the database, but trying to look through about 9000 rows makes me go crosseyed eventually. I was hoping there's a way to get excel to simply change the color of the cell (or alternate highlighting and not) when the phone number changes.

So for example:

Steve 555-1212
Barry 555-7000
Martin 555-7000
Clara 555-1345
Emily 555-1345
Sandra 555-6754
Pete 555-7890

Something along those lines, only with the cell color changing, not the text, so it's easily identifiable which numbers are the same going down the list (which appear to either be duplicates or the same number for multiple rows) and a way to identify when the numbers change at a glance.

Is this doable?

Hi,
I have produced a pivot report that automatically updates a bar chart when the source data is changed.
I have 2 colours:

Green = Pass
Red = Fail

I have modified the order of colours (using tools, options, color etc...) so that red is the first selected color and green the second which works fine when both pass and fail are present on the chart however when the scenario arises whereby a result is only a pass (green) the chart selects the first color (red).
Does anyone have any suggestions for hardcoding the colors to the results using vb?

If I have confused anything please let me know.

Regards Dave

please, is it possible to change font style, size, color of page number
watermark in page brake preview?
also is it possible to delete or change page number watermark in page break
preview?

Hello,

I have a chart that has 3 different series. What i'd like to do is have a macro that goes through series 2 only of this chart and check all the values of this series in an excel range( lets say it's called "range2") and based on those values change the color of the series in the chart. So for example if the value in the range is positive then green and if it's negative then red.

Can someone tell me how to do this ? This is all i have so far

Sub Test()

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(2).Select

The following code changes each line series from 2.25 point to .75 point. It should also change the color of each line to a dark green but it doesn't. The chart stays multicolored unless I run it a second time. Why does it not change the line thickness and color all at the same time? I don't want to have to call this module twice to get it to do the job.

Sub x()
    Dim objSeries As Series
    
    With ActiveChart
        For Each objSeries In .SeriesCollection
            With objSeries.Format.Line
                .Transparency = 0
                .Weight = 0.75
                .ForeColor.RGB = RGB(40, 56, 24)
            End With
        Next
    End With
End Sub


Hi,

I have a scatter chart with five different series. I want to name the data points with their labels in all the series'. I have a code which works just fine with one series, but dies when I try it for multiple series. Can someone please help me with this... searched a lot on the net, but couldn't find any code which does this...

The code that works for one series is:


	VB:
	
 AttachLabelsToPoints1() 
     '   Dimension variables.
    Dim Cht As Chart 
    Dim xVals As String 
    Dim Counter As Integer 
     '   Disable screen updating while the subroutine is run.
    Application.ScreenUpdating = False 
    Set Cht = ActiveWorkbook.Worksheets("B'berg Links").ChartObjects("Chart 1").Chart 
     '   Store the formula for the first series in "xVals".
    xVals = Cht.SeriesCollection(1).Formula 
     '   Extract the range for the data from xVals.
    xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, Mid(Left(xVals, InStr(xVals, "!") - 1), 9))) 
    xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1) 
    Do While Left(xVals, 1) = "," 
        xVals = Mid(xVals, 2) 
    Loop 
     '   Attach a label to each data point in the chart.
    For Counter = 1 To Range(xVals).Cells.Count 
        Cht.SeriesCollection(1).Points(Counter).HasDataLabel = True 
        Cht.SeriesCollection(1).Points(Counter).DataLabel.Text = Range(xVals).Cells(Counter, -3).Value 
    Next Counter 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data

I tried using VLOOKUP which is not working as I am having multiple values in column A.
Any help and suggestions

Hi. I am new to this forum and also new to macros to MS Excel. However, I do have a basic understanding of C++/VB codes and structures. So I really appreciate any help anyone can offer.

I currently am working on writing a macro for Excel to open a text data file and using the the data to make a graph. I already finished writing the part for opening and importing the file. However I am having trouble do the graphing part.

The data is in 3 columns. One is for x-axis (Column 1), one is for y-axis (Column 2) and one is for the IDs (Column 3). There are undefined number of IDs and the number entries under the same the IDs can vary, however they are all grouped together.

For example, the three columns would look like something below (sorry, I don't know how to embed the file in...):

Col1 (x)______Col2 (y)______Col3 (ID)
25.05______218.24______244
349.18______218.82______244
399.66______218.64______244
450.90______217.68______244
800.62______195.84______244
1200.61______160.37______244
24.94______215.09______686
50.91______214.91______686
99.97______214.12______686
150.45______213.16______686
401.91______204.67______686
450.16______202.09______686
800.66______178.10______686
1202.50______146.92______686
24.98______218.56______351
50.43______218.48______351
99.97______218.24______351
149.62______217.97______351

...and so on. There could be as few as one ID that only have one entry in the file or as many as 100 IDs with 100 entries under each of them. The part I cannot figure out is how to graph all the Column 1 vs Column 2 data with the same IDs (same values in Column 3) on the same graph.

Thanks for any help that you guys can offer.
Edit/Delete Message

I have a Stacked Column chart with many series (and only 2 observations per series). I would like to change the fill and border of all the series to be white fill with a solid black border (I will activate data labels to distinguish each one).

I can change the fill and border of each series, one by one, but there are quite a bit of them. Is there a way of selecting ALL THE SERIES and then applying the same formatting to all of them? Failing that, would anybody have a VBA script that can do something like that?

Thanks,

Ben

I do a lot of charts that contain spectral data. I'll have 20-30 data series
and I plot them as a XY scatter using the line only format. However the
default thickness in XL 2007 is way too fat and consequently I loose any
ability to differentiate the series. So far the only way I've been able to
change this is by actually selecting each individual series and changing the
thickness. Is there a way to change the default line thickness to 1/2 pt?
Or at least to change all the series in a chart in one go?

I get error like "Size method not allowed by Font object" for this code. Can
sombodey help me?

/Regards

-----
"John Green" > skrev i meddelandet
...
> Try the following:

Sub SetFonts()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim chtobj As ChartObject
Dim scol As Series
Dim dl As DataLabel
For Each chtobj In ActiveSheet.ChartObjects
For Each scol In chtobj.Chart.SeriesCollection
For Each dl In scol.datalabels
With dl.Font
.Name = "Arial"
.FontStyle = "Fet"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.Background = xlTransparent
End With
With dl
.NumberFormat = "#,##0"
.AutoScaleFont = True
End With
Next
Next
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

> John Green
> Sydney
> Australia
>
>
> "Marie J-son" > wrote in message
> ...
>> Hi,
>> This Sub doesn't work (why?)
>>
>> I have a number of chartobjects with different numbers of datalabels and
>> seriecollections. How can I change font size for all charts and
>> datalabels
>> in seriescollections all in a row?
>>
>> Sub SetFonts()
>> Dim chtobj As ChartObjects
>> Dim scol As SeriesCollection
>> Dim dl As DataLabel
>> For Each chtobj In ActiveSheet
>> For Each scol In chtobj
>> For Each dl In scol
>> With dl.Font
>> .Name = "Arial"
>> .FontStyle = "Fet"
>> .Size = 16
>> .Strikethrough = False
>> .Superscript = False
>> .Subscript = False
>> .OutlineFont = False
>> .Shadow = False
>> .Underline = xlUnderlineStyleNone
>> .ColorIndex = xlAutomatic
>> .Background = xlAutomatic
>> .NumberFormat = "#,##0"
>> .AutoScaleFont = True
>> End With
>> Next
>> Next
>> Next
>> End Sub
>>
>>
>> Kind regards
>>
>>
>
>

The process for changing page size for multiple tabs within a spreadsheet has changed from Excel 2003 to 2007. In 2007, in order to do this, you open the spreadsheet, right click on the first tab, choose select all sheets, then go to the top menu and select page layout and then select the size that you want all sheets to be. This has been extremely helpful to me to discover as I am in the US and receive multiple reports from Europe that are formatted on A4 that I need to change to 8 1/2 x 11.

I have datapoints in a bubble chart ("Mckinsey_Chart") I am changing to pie charts (named pie_1 etc). Each datapoint has a different chart.

The first series changes correctly, but then the code gives me an error: series
Error 1004 Unable to get the points property of the series class. Any ideas? Thank you

this is the code I am using:

For i = 1 To ActiveChart.SeriesCollection(1).Points.Count
' get the pie chart that corresponds to the chart point
ThisWorkbook.Worksheets("setup").ChartObjects("Pie_" & i).Activate
ActiveChart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
' paste it in to the series point
ThisWorkbook.Worksheets("mckinsey").ChartObjects("Mckinsey_Chart").Activate
ActiveChart.SeriesCollection(1).Points(i).Paste

Next
i = i + 1
For i = 5 To 8
' get the pie chart that corresponds to the chart point
ThisWorkbook.Worksheets("setup").ChartObjects("Pie_" & i).Activate
ActiveChart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
' paste it in to the series point
ThisWorkbook.Worksheets("mckinsey").ChartObjects("Mckinsey_Chart").Activate
ActiveChart.SeriesCollection(2).Points(i).Paste

Next

I have a worksheet function as follows: ="the rate is special and equals
" & average(a1:a3)

I want to change the font style and color of the word "special" in the
character string to bold and green. I understand it can be done with event
macros. Thanking you in anticpiation, .... Greg

On a column chart in Excel each column has a different color. The name of
each category is shown in the legend along with a small box (legend key) with
the color corresponding to the color of the column for that entry in the
chart. My question is how to I enlarge those little color boxes next to the
names in the legend? My color boxes are so small I can't see what the color
is. Color boxes are way smaller than font size on the adjoining text.

I have a worksheet function as follows: ="the rate is special and equals
" & average(a1:a3)

I want to change the font style and color of the word "special" in the
character string to bold and green. I understand it can be done with event
macros. Thanking you in anticpiation, .... Greg

Hi All,

The crooks of what I want to achieve is to use one workbook with search values in Column A on the first sheet, to search another workbook for all those values.

I have a few workbooks with multiple sheets in detailing a large list of products on each one.

The workbooks are in the same directory as the one I want to use as my Search workbook "searchsheet.xlsx"

My Directory has these files in:-
Searchsheet.xlsx (This is the one I want to use to search and will contain the macro)
Products20-30.xlsx |
Products423-223.xlsx | These contain the products
Products12-24.xls |

On "searchsheet" I have a list of about 100 products in 100 cells in column A (this list of products will change).

I want to create a macro on that book which runs from a VB button that will search a selected workbook (which I can select from the 3above (or more)) for all 100 items in Column A. I want it to search for part contents of cells too, not an exact match.

If it finds any matches I want it to return a value of 1 in Cell C5 and if it doesn't match I want it to return a value of -1.

The idea is that if Cell C5 returns a 1 I will use Conditional formatting to make the cell Red and if it returns -1 then it will turn Green.

Any ideas are greatly appreciated!
Thanks

I want to create a worksheet with numerous pages of the same formating. I can
copy & Paste font style, size color, etc.
My problem is I would like to create a page witch contains 3 different row
heights and column widths. I, then, want that page to reapeat, possibly,
hundreds of times.

19A14D1N

I am trying to change the page set up setiings to fit to 1 page for 56
worksheets within a workbook. Is there an easy way to do this? Can I buy a
program add in to do this for me?

Dear Forum,

I recall seeing a COUNTIFS formula once that utilised the curly parentheses to search for multiple values within a single column but am struggling to repeat the same. I am developing a spreadsheet to lookup multiple values (sample below) within a range and then count how many were completed on a particular date. So far my formula looks like this:

=countifs('Raw Data'!B2:B10000,"Y",'Raw Data'!C2:C10000,D11,'Raw Data'!A2:AC10000={A6:A40})

where "Y" determines if it's complete, cell D11 holds the date on which it was completed and the range in the {} equals the list below.

EXTRACT OF LIST OF VALUES
041-12
075-02
413-01
416-01
418-07
418-09
437-01
470-02
655-06
............etc.

I've attached a sample workbook for clarity.

I want to use same header for multiple sheets in Excel, and only change a few
items in the header, or footer; i.e. summary of billing for separate
projects using the same content headers for the body of the sheet, but the
header changes to note a specific project. I can Copy & Paste the sheet
content and then modify from sheet to sheet; but I did not find a ay to copy
the Header & Footer from sheet to sheet without having to re-enter all the
Header/Footer info again. Can it be done??