• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using macro to color a shape with nice Excel 2007 colors?

M

mvirgilio53

New Member
So I made a macro that colors certain shapes (terratories in the U.S.- poor man's GIS) based on numbers in cells. It works great, except that I'm forced to choose from the old, unappealing Excel 2003 colors. Is it possible to select the nice excel 2007 colors instead?
 
Yes

Record a macro and fill a few shapes with various colors you want, manually using the color chooser.

Look at the code and you will see a different color statement than was used in 2003
 
I don't have any problem recording macros for cells, but when I record macro and format shapes, nothing happens. The code stays blank. What am I doing wrong?


Thanks
 
You done nothing wrong,

The Excel object model hasn't been fully implemented in the macro recorder and so the code isn't recorded. It is much better in 2010 although a few small gaps still exist.


However there is a solution:


Add a shape to your workbook and take note of the shapes name in the Name Box (Upper Left of Workarea)

Add the following code to a module in VBA

[pre]
Code: 
Sub Show_Shape_Colors()
Dim shapename As String
Dim txt1 As String, txt2 As String

shapename = "Rectangle 2" ' Change to suit
txt1 = "Back color =" & Worksheets(1).Shapes(shapename).Fill.BackColor.RGB
txt2 = "Fore color =" & Worksheets(1).Shapes(shapename).Fill.ForeColor.RGB

MsgBox txt1 + Chr(13) + txt2
End Sub

Change the shapename = "Rectangle 2" line to suit your shapes name


Go back to Excel, Right click your shape and apply the macro to your shape


Select the shape with a Right Click and change the colors and then click the shape


a Msgbox will show you the foreground and back ground colors.

To apply the colors to a shape

Worksheets(1).Shapes(shapename).Fill.ForeColor.RGB = 15478521
Worksheets(1).Shapes(shapename).Fill.BackColor.RGB = 12345678
[/pre]
 
So that message box gives me the backcolor and forecolor, but can that be applied to this type code?

[pre]
Code: 
With ActiveSheet.Shapes("GC SC")
If Range("Ai5").Value = 1 Then
.Fill.ForeColor.SchemeColor = 10
ElseIf Range("Ai5").Value = 2 Then
.Fill.ForeColor.SchemeColor = 52
ElseIf Range("Ai5").Value = 3 Then
.Fill.ForeColor.SchemeColor = 13
ElseIf Range("Ai5").Value = 4 Then
.Fill.ForeColor.SchemeColor = 44
ElseIf Range("Ai5").Value = 5 Then
.Fill.ForeColor.SchemeColor = 57
End If
[/pre]

The numbers that the messagbox gives me can't be used where the color palette codes are. Thanks.
 
No

It will have to be something like

.Fill.ForeColor.RGB = 123456789
 
You must log in or register to reply here.
Back
Top