Format Major Gridlines with VBA in Excel 2010

[jarrah31]

I would like to use Excel VBA to format the line color of major gridlines in various charts please. Unfortunately recording a macro while performing the actions results in the following:

Please Login or Register  to view this content.

I basically want to create a Gradient Line, and to do this manually I follow these steps:

1. Select the gridlines on my chart
2. Right-click and choose Format Gridlines...
3. Within Line Color, select Gradient line
4. Stop 1 of 3:
   -Grey -50%
   -Brightness -25%
   -Transparancy 100%
5. Stop 2 of 3:
   -Grey -25%
   -Brightness -15%
   -Transparancy 0%
6. Stop 3 of 3:
   -White
   -Brightness -0%
   -Transparancy 100%
7. Angle 0

Can this be done programatically? Any help very much appreciated.

[lokicl]

Hi,

In the above code - take out the last 2 lines and use the with loop.

Please Login or Register  to view this content.

Now - update the colors of your choice.

Regards,
lokicl

[jarrah31]

Thank you for your help Lokicl - does this code apply a gradient colour as it seems to only apply a solid colour?

As per my example, I need to set a specific colour, brightness, and transparency for each element in the gradient line.

Thanks.

[lokicl]

Hi,

You have update the color index number and RGB color (0,255,0) for different colors and combinations.

Regards,
lokicl

[hishamshamy]

Hi,
Sorry for breaking the rules but
I had a big problem can't wait to help me solve it
Iam working in steel fabrication and sheets company that use large amount of sizes in the same item , like steel sheet width 1.25 meter length 5 meter cut it into Defferent sizes i want excel to change the original item to the sizes i got after cutting and give me the summary inventory .
Thanks,
Hisham shamy

[jarrah31]

Hi,

You have update the color index number and RGB color (0,255,0) for different colors and combinations.

Regards,
lokicl

Oh I see - thanks I'll give that a go!

Ignore that other post from hishamshamy, completely unrelated to my question....

[hishamshamy]

Hi
If you don't mind where can i get my answer ?
Thanks

[jarrah31]

Start a new thread.

[jarrah31]

Thank you for your help, but it's not working the way I was hoping.

When I run through the loop, all it does is change the major gridlines to the specified colour - blue first, then green, then orange, with no gradient being applied.

Hopefully these screenshots help explain what I'm trying to do:

majorGridlines.png
majorGridlines2.png

(except that image uploader doesn't appear to work)

[Andy Pope]

I don't think this is supported in the object model.

Line format does not have the GradientStops properties that the FillFormat does.

[jarrah31]

ahh I see - shame.

Thanks for your help!