Conditionally formatting Excel charts

[Freckles]

This is a double post with "Microsoft: Office"

I am charting three types of data: Actuals, original forecast and current forecast. If I use three different series, then the months without actuals have a space between original forecast and current forecast (only original forecast is showing when actuals are present). By using an if statement, I can get down to two series (original forecast and actual/current forecast combined), but when the current forecast is showing, it needs to be different color, etc.

How can I conditionally format a chart's colors? For instance, (if X = 7, format data series "red upward wide diagonal", format data series "gold solid&quot

I know that is not the correct terminology, but should make my problem understandable (I hope). And I do need what I would need to put in for "red upward wide diagonal". : Deb Koplen
deb.koplen@verizon.com

A person can stand almost anything except a succession of ordinary days.

 

[mptrew]

This probably isn't the best method of doing what you require but I found it by trial and error

I call the subroutine below from my if statement, passing the row and coloumn to be formatted (frow, fcol) and this allows me to have several colours fonts etc within a single cell.

Sub format_cell(frow, fcol)
Cells(frow, fcol).Select
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Wingdings 3"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With ActiveCell.Characters(Start:=3, Length:=30).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
End Sub


Hope it helps,

Martin

 

[Freckles]

\Thanks so much for the information. However, my knowledge regarding "subroutines" is very limited. Could you please advise a little more of the "how-tos"? And will this work for a chart???? : Deb Koplen
deb.koplen@verizon.com

A person can stand almost anything except a succession of ordinary days.

 

[mptrew]

Firstly, yes this does work with charts, change activecell. to activechart.

An easy way to call the above subroutine would be to draw a button on your spreadsheet and then attach it to the macro in the tools->macro->macros pull down menu.