Format Shape With ThemeColors

I can format shapes with RGB
    ActiveSheet.Shapes("Test").Fill.ForeColor.RGB = RGB(128, 128, 128)
Is there a way to format a shape using themecolors in the same way you can 
format a cell
    With Range("A1").Interior
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
    End With
 I have tried every variation of the code & syntax to no avail - could 
someone give me an example of the code to do this please - thankyou in 
12/3/2009 5:46:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 56

This is the sort of syntax you are looking for:

     With Selection.ShapeRange.Fill
         .Visible = msoTrue
         .ForeColor.ObjectThemeColor = msoThemeColorAccent3
         .ForeColor.TintAndShade = 0.8
         .Transparency = 0
     End With
     With Selection.ShapeRange.Line
         .Visible = msoTrue
         .ForeColor.ObjectThemeColor = msoThemeColorAccent6
         .ForeColor.TintAndShade = 0.4
         .Transparency = 0
     End With

You can drop things like transparency and solid if you won't need to 
change them, but sometimes it is better to be sure (eg colours will look 
wrong if the transparency is not 0).
For TintAndShade I tend to use rounded numbers, although they won't 
exactly match the choices in the gallery (but it also means you have 
easy access to a wider range if you want to, while remaining 'theme aware')

Hope this helps


Roger wrote:
> I can format shapes with RGB
>     ActiveSheet.Shapes("Test").Fill.ForeColor.RGB = RGB(128, 128, 128)
> Is there a way to format a shape using themecolors in the same way you can 
> format a cell
>     With Range("A1").Interior
>         .ThemeColor = xlThemeColorAccent3
>         .TintAndShade = 0.799981688894314
>     End With
>  I have tried every variation of the code & syntax to no avail - could 
> someone give me an example of the code to do this please - thankyou in 
> anticipation
12/4/2009 10:55:10 AM

Similar Artilces:

Changing format of data
We're trying to update the database, change some fields from *text* to *date/time*, add a few fields, remove some. The bulk of the data will stay the same. When I tried to just change the database, it lost all the old data (I was working with a copy). I didn't design the origanal database, but now I've got to fix it. It's pretty big, I don't know if manual work is really an option. Thank you! Hi Patrick, Keep working with a copy for safety's sake. If simply changing the field type doesn't give the results you need, do it this way: 1) add a new field of th...

Formating Content on reports
I've tried following the steps in the SDK but am getting no where. How do I apply formating to numbers, dates ETC? I've added a dataset for "DSNumandCurrency" with a SELECT * FROM dbo.fn_GetFormatStrings() but can't seem to use anything. ...

Format Cells (Number)
I can't change the following date to a different Number format. [Format Cells>Number...] How could I change it to like, 12/26/00 or 36886? 26-Dec-2000 This is the beginning of a series of date and doesn't let me create any valid X axis. Thanks. Hi the cell is probably formated as text. Try the following: - format the column as 'Date' - enter the value 1 in an empty cell - copy this cell (CTRL+C) - select your column of dates - goto 'Edit-Paste Special' and choose 'Multiply' as action -- Regards Frank Kabel Frankfurt, Germany wlotto wrote: > I ca...

How can I format a cell on a specific date?
I have a spreadsheet that has the following: Column 1 = Person's name Column 2 = Amount due I need cells to change colour if the amount due is not paid by the 20th of the month. I have managed to figure out the date bit but unsure of what to put for the value as the amount due for each person is different. I hope this makes sense. Any help would be greatly appreciated. I think you must have other columns apart from the 2 you refer to. For example, do you have an Amount Paid column, and a Date Paid column? If you don't, then how will Excel know if the debt has been...

How do get rid of formatting characters when reporting Issues from reporting database
Issue description and Issue Resolution because they are rich text (I guess) when reported via SQL Reporting Services contains all kinds of garbage like </div>, &nbsp, etc. What's the best way to strip the special characters out when reporting from the database? Thanks, Andy Novak UNT Hi Andy, you have a couple of options depending on your SQL version. If you run 2008 you can use the REport builder 2.0 which by the way renders the rich text. If you have 2005 you will need to write some code to strip the HTML tags out. Hope that helps -- Marc Soester [MV...

Custom Number Format for Weight
I have a formula that I would like to display the result as pounds and ounces. I created and custom format that will display as (for example) "14.25 lbs" but I would like it to display as 14 lbs 4 oz. It must display as a number and not text. Any help would be great! Thanks, Tom, No can do with a format, methinks. How about displaying pounds and ounces in another cell (column)? =INT(A2) & " lbs " & MOD(A2,1)*16 & " oz" This yields a text string, so you can't do any math on it; use the original (A2) cell for that. -- Earl Kiosterud mvpear...

Outlook 2003 does not export all fields to any of the selected file formats #2
I just came across the message below while trying to answer the same question. No completely satisfying answer was posted at the time. I can add a solution but it raises another problem. You can link to or import a message folder from Access. That brings in the date fields but you lose the From address. It shows the name but not the underlying address. For example, in the case below, the sender would be shown as "Great Eyes" but the actual hotmail address would not be shown. Does anyone have a solution for getting messages into some sort of workable format (flat file, Excel, A...

Phantom Column border
Win 2K Office 2K Hi folks, I have a unique problem, which I haven't seen before and can't correct. I have a workbook, broken out in 3 worksheets. Sheet one lists daily nationwide gaming results & includes graphs on the average results on a nightly basis. Sheet two, contains the same data via copy - paste special, with the daily data broken out by month. This is the sheet that has the formatting problem. This is a two page report, with Jan thru June on one page and July through December on the next page. I have three pages after that with graphs on the nightly results broken out ...

Convert mm/dd/yyyy to yyyymmdd Date format in Excel
Seems like it should be easy but I am stuck. Any easy way to convert mm/dd/yyyy format in column to yyyymmdd forma some of the values in my database column for mm or dd have only 1 digit, and would need 0 fillers in order to comply with my final database need Help Thanks in advance Click FORMAT > CELLS > Custom. In the text box in the middle of the dialog box type yyyymmdd then click OK. In that cell(s) if you type 1/20/2003 then hit enter 20030120 will be displayed. Marty >-----Original Message----- >Seems like it should be easy but I am stuck.. > >Any easy w...

Formatted numbers problem
When I open reports in excel, I often see numbers that read 1.00043E +11, apparently indicating that there is a number format problem. When I change the cell format (to text or number), I am usually able to see the full value in the cell. However, I must first physically enter each cell (i.e., selecting the cell, and then double clicking, and then pressing enter) in order to accomplish this effect of seeing the full value. Is there a way to get around this? Thanks for any suggestions. How about using Number in the format|cells|Number tab|number category Show the 1000's separator and ...

how do I zoom in a shape only
I am making a floor plan layout and labeling all the network jack locations. I am using a simple box to do this with text in it. What I want to do is make the box so when I click it, it will zoom in (like a popout or something) only the box though and not the whole drawing. So it will not show other labels of other items but only the item you click on. Like if it could be a hyperlink or something like that thanks consider making your shape with a link to another page in your Visio file with only the info you desire and at a large have complete control Doug.S "Lannyd...

Customized Format
At a minimum, I'd like to enter: 1st 2nd 3rd 4th etc. and have the cells formatted as: 1st of the month 2nd of the month 3rd of the month 4th of the month etc. Ideally, I'd like the cells formatted the same after entering only the number. Does someone have a suggestion? To just enter numbers, you could use a formula in another cell - enter a number in cell A2, and use =A2 & IF(A2=31,"st",CHOOSE(MIN(MOD(A2,20),4)+1,"th","st","nd","rd","th"))& " of the month" Or enter 1st, 2nd ...

Date Format Challenge
Hi Folks - I'm using Excel 2002. Try this challenge in a new blank worksheet: Enter 10:15 in A1 Enter 10:30 in A2 Enter 10:45 in A3 Enter 1 in A4 Excel will automatically treat the 1 as a date. Why? I did not format the column as a date. Or, is Excel looking at the previous entries and assumes I need a date. Thansk. Mike Not for me, if I do that it returns 00:00 in A4 as expected since it thinks I enter times and 1 is 24/24 -- Regards, Peo Sjoblom "Michael" <> wrote in message news:Qo_Kb.29501$JD6.21927@lakeread04... > Hi Folks -...

Format Painter just won't work
When ever I highlight a text box that has the format I want to use somewhere else in the presnetation and click on Format Painter and then click on the text I want to change ... NOTHING Happens. This is in PP 2007 and VISTA. Why? Could follow that methodology in all previous PP versions. Thanks! On Mar 10, 10:33=A0am, JR <> wrote: > When ever I highlight a text box that has the format I want to use somewh= ere > else in the presnetation and click on Format Painter and then click on th= e > text I want to change ... NOTHING Hap...

Can't select a range after moving a shape object
Caveat: I know this code isn't very elegant, but I'm under a deadline. I've got three stacked objects on a report: two logos, and a white rectangle. I'm running a macro that pushes the white rectangle to the back, then pushes one of the logos to the back, effectively making only one of them visible. The macro is basically a toggle for the user to select which logo they want visible. The macro code goes like this: Sheets("Run Avg Sheet").Select ActiveSheet.Shapes("Rectangle 54").Select Selection.ShapeRange.ZOrder msoSendToBack If Comp = &...

Format Cell #2
Can someone let me know why to batch convert a set of column data fro number format to time format in xx:xx:xx e.g. 1223 -> 12:23:00 Thank -- Message posted from Hi Using a helper column put in: =VALUE(LEFT(A2,2)&":"&RIGHT(A2,2)) and format the result as time. If you are happy with the result, don't forget to Edit / Copy and Edit / Paste Special Values to fix the figures. -- Andy. "eycf >" <<> wrote in message > Can someone let me k...

Exporting to a txt file in a certain format.
I have an excel sheet that I need to export into a certain format. I will need to have it so each column will only export the first (x) amount of characters in each cell, then add a SPACE, then add the first (x) amount of characters from teh next cell. How do I do that? Hi, John- Suppose your existing data is in a tab called Sheet1; cell A1 contains the text string ABCDE; cell B1 contains a number 12345. Insert a new tab in the workbook- for purposes of this example I used the default name Sheet2. In cell Sheet2!A1 enter this formula: =MID(Sheet1!A1,1,3)&" "&MID(Sheet1!...

data label format not saving in excel 2010
Hi, Long time reader, first time poster. I hope you all can help. I'm trying to format data labels in a 2 axis column and line chart in excel 2010. The chart data is drawn from a different document, and in that document all of the numbers used in the chart are formatted as I'd like them to be seen in the chart (ex. a click-thru rate is formatted as 13.4% instead of .134.) I have succeeded in formatting my data labels in the chart correctly, but whenever I open the document containing the chart without the document containing the data, the formatting is lost and I...

Conditional Formatting
Is it possible to apply conditional formatting from spreadsheet cells to a chart? I have a set of scores formatted in RED-YELLOW-GREEN fashion, like a stop light. I would like to show these color ranges in the chart in the grid area. It is not directly possible to do this. You can make a conditional chart, which uses different series to represent the different formats. The algorithms used to separate the data into separate series must match what drives the conditional formatting in the worksheet. Conditional Charts: - Jon --...

how do you format a cell to be in degrees
I want to find the cosine of an angle, but the computer doesn't know the value I am referencing is in the form of a degree. can anyone help me!! you have to change it to radians in your formula =cos(Val*pi()/180) "justin" wrote: > I want to find the cosine of an angle, but the computer doesn't know the > value I am referencing is in the form of a degree. > > can anyone help me!! This may help you: Insert a degree symbol Help You can easily insert a degree symbol in your Office documents. Most fonts that Microsoft Windows® users have installed inclu...

Carrying format with cell reference to another sheet.
In my spreadsheet column headings are days and row headings are group titles. Each group goes through different classes each day. It is easiest to coordinate which group attends each class with the groups stacked as described but it is harder for the group leaders to print the calendar because it is so long. I have created formulas to bring the data into a traditional calendar format on an adjacent sheet but the formatting does not follow. Is there a way to force the formatting to follow. The formula currently stands as: =IF('Sync Matrix'! CN1>1,'Sync Matrix'!CN1,""...

Yes/No Format Issue
Hi Groupies I have been building databases for years and have not run into this problem before. This 2007 database I am working in has a few yes/no fields. When I add these fields to a Form, they are show up as textboxes instead of the yes/no check box I am expecting. They respond correctly in queries, autoreports and autoforms but when I add it manually, I get Yes or No and have to manually change it to a Check Box Any ideas? Anybody else having the same issue? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself,...

Conditional formatting of dates in two cells using Excel 2007
Hi, I've got a spreadsheet set up as: Column A - task Column B - Expected completion date Column C - Actual completion date and I want to set it up so that all tasks that have not yet been completed but should have been are highlighted in red (that is, for example, if B2< todays date and C2 is blank then A2 is highlighted red). I've been messing about but can't seem to get it right in the conditional formatting - any suggestions? thanks Select cell A2 Select conditional format -> formula Enter the following forumla =AND(B2<TODAY(),C2=&qu...

How to Change Displayed on Master Visio Document Customer Shape Properties.
In Visio 2003, how can you change the shape properties that display on the main Visio document? For example, if you use the Raid Array Shape and fill out the 'U' or 'IP' information, it will display on the main document. I would like to also add the network name. Thanks in advance. so how did you get the ip address to display? Did you enter it into the text field? The units field is a special case. al "Master Tester" <> wrote in message > In Visio 2003, how can you change th...

Conditional Formating Question
Can I conditional format a certain cell based on a different cells value, for example: I have 3 different colors that show up in cell D7, but I have a value in D8 already that I do not want to change, but I want the color of D8 to be the color that shows up in D7. So... If D7 equals Green then I want the background of D8 to be Green as well, but if D7 equals Red then D8's background would be Red, and so on. I don't know if I can solve this through conditional formatting or if there is some VBA that would do this. Any help? Thanks, TyeJae --- Message posted from http://www.Excel...