MarkerForegroundColor not working

[mole6e23]

Hi, I'm using the following code to try and set the foreground and
background color of my markers, but I get nothing. When I add the line
in about changing marker size, that does work. Does anyone have any
suggestions as to what I may be doing wrong?

Sub TestMarkerColor()
With ActiveChart.SeriesCollection(1)
.MarkerForegroundColor = RGB(255, 0, 0)
.MarkerBackgroundColor = RGB(255, 0, 0)
'.MarkerSize = 5 + 10 * Rnd
End With
End Sub

I'm running Excel 2004 v11.3 on the Mac.

Thanks in advance,
Todd

 

[Peter T]

Hi Todd,

The code looks OK but how would you see the Foreground when applying same
colour to Background

try testing with
..MarkerBackgroundColorIndex = xlNone

Regards,
Peter T

 

[Jon Peltier]

Peter - One's the outline and one's the fill for markers like a square or
circle.

Todd - Keep in mind that Excel will only apply the colorindex of the color
in the palette that's closest to the RGB you enter. Granted, red is one of
the default colors, but try .MarkerForegoundColorIndex as well.

- Jon

 

[Peter T]

Peter - One's the outline and one's the fill for markers like a square or
circle.

I appreciate that but it seemed to me that if the OP is applying same colour
to Background fill he won't see the Foreground outline. Thought perhaps if
applied no colour or a different colour to the background the foreground
change would be more obvious. Alternatively pehaps apply MarkerStyle xlDot
or xlDash which don't show background fill.

Regards,
Peter T

 

[mole6e23]

Jon and Peter -

Thanks for the tips. I have used the .MarkerForegroundColorIndex as
well, and that doesn't seem to work (note - by the end of the message I
worked out a method that works, but I'd still love some insight to what
is going on). Using Peter's tip to change to xlNone, I can get the
background or foreground color to change to none, but I cannot get it
to change index repeatably. I put in some debug lines into the code:

Sub TestMarkerSize()
With ActiveChart.SeriesCollection(1)
Debug.Print "Start ForegroundColorIndex: ";
..MarkerForegroundColorIndex
Debug.Print "Start BackgroundColorIndex: ";
..MarkerBackgroundColorIndex
.MarkerBackgroundColorIndex = 12
.MarkerForegroundColorIndex = xlNone
Debug.Print "End ForegroundColorIndex: ";
..MarkerForegroundColorIndex
Debug.Print "End BackgroundColorIndex: ";
..MarkerBackgroundColorIndex
.MarkerSize = 5 + 10 * Rnd
End With
End Sub


The result of running this is mixed. It's difficult to figure all of it
out. But the trend seems to be that the first time I run the code on a
fresh plot, it works, I get:

Start ForegroundColorIndex: -4105
Start BackgroundColorIndex: -4105
End ForegroundColorIndex: -4142
End BackgroundColorIndex: 12

and the colors change appropriately (switching from xlAutomatic, which
I figured out was the -4105). However, all subsequent times that I run
it, it does not work (and does not seem to work at all in the context
of my larger code where I'm changing a lot of chart parameters.) If I
use the same plot as just above, and run the code again, changing the
color index to 18 from 12, I get:

Start ForegroundColorIndex: -4142
Start BackgroundColorIndex: 12
End ForegroundColorIndex: -4142
End BackgroundColorIndex: 12

And all subsequent efforts to change the index don't work. And like I
said, it doesn't seem to work at all in the context of my larger code
base.

To add to the fray, if I switch out the xlNone to be to the background
color index and set the foreground color index to 18, I get:

Start ForegroundColorIndex: -4142
Start BackgroundColorIndex: 12
End ForegroundColorIndex: 1
End BackgroundColorIndex: -4142

So, it seems very odd. However, while writing this message, I noticed
this pattern and tried the following code:

.MarkerForegroundColorIndex = xlNone
.MarkerBackgroundColorIndex = xlNone
.MarkerBackgroundColorIndex = 12

If I use that, I can now correctly set the background color index, but
I don't really understand why I need to reset it to xlNone every time
before setting it. So I have code that works now, but I'd still love
some understanding of it if anyone has anything to offer.

Thanks for the help!
Todd

 

[Peter T]

Hi Todd,

It seems I misinterpreted your problem, thought it was a case of something
like applying red font on red fill and wondering why you couldn't see the
red font.

Not sure why it's not working for you but perhaps it's related to other
things you are doing, you said - "and does not seem to work at all in the
context of my larger code where I'm changing a lot of chart parameters." If
that involves changing the Line (ie border) that will reset your markers to
same - are you doing that. Similarly if you format individual points they
will be over ridden by any subsequent change to the entire series.

I'd start by simplifying your test and concentrate only on foreground or
background by making one property invisible (xlNone) and applying obvious
colorindex's, eg 3,4,5,6. And don't introduce any other code!

FYI The automatic -4105 format is the colour of colorindex from 17 up (line
types) relating to series in order added. -4142 is xlNone, invisible.

Regards,
Peter T

Without knowing whatelse you are doing not sure why