Today, in Episode #1336, Bill discusses the new color themes in Excel 2010. The new themes come with six (6) accent colors - but today, Bill shows us how to take those six theme colors and turn them into 1200 colors using Tint and Shade.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel VBA, chapter 17.
Tint and Shade.
Okay, so chapter 17 is all about sparklines.
Why am I not doing spark lines in the podcast?
Because I love, when I discover something new and cool and I was looking at colors, you know we always used to use, while color index back in Excel 2003 from one to 56, or you know, RGB.
You know, but now the macro recorder starts doing this, the theme color all right, you know in the themes I get it page layout, you can choose a theme and each theme has six colors.
You can see, how the colors change back there.
All right, and we can even define it a new theme, each theme is comprised of six accent colors and then we see these a lot, back on the home tab and either the font color or the fill color.
The six accent colors appear here.
But what's interesting is this lighter eighty percent, lighter sixty percent, lighter forty percent.
Darker twenty five percent, darker fifty percent.
So, I think about those six accent colors are shown with five different variations there.
Now, I love when I discover the VBA does something better than the Excel interface does and that was exactly, what happened here because when I actually looked at this code, we have theme color, XL theme color accent 1 through 6.
Those are the six for the theme.
But then tint shade turns out that it can be a number anywhere from negative .99, all the way up to positive .99.
The negative numbers are darker, the lighter numbers are the positive numbers are lighter and so I actually wrote a little macro here that went through and looked at the hundred different darker values.
So, this is the orange, that is our theme color 6 but it goes all the way down to black, depending on what tint and shade we use and I'll scroll down here.
Here's a another little macro I wrote that, came up with the 99 lighter colors.
All right, so that one orange color goes from completely black to completely white in 200 different steps.
So, using tint in shades to modify the theme accent color.
You're not limited just those five, that they happen to give us.
Use and again, positive and negative members here.
Let's try, it will choose a cell over here now, in this case theme accent color 1 is blue.
So, we'll just use a tint and shade of zero.
First to see how that looks, we'll run that code and you see we get that blue, all right.
But then if we would use a tint shade, let's go darker, so minus point 75.
Run that!
Alright, so oh! That's almost black, almost too dark.
So, how about minus .5.
That one.
Okay, so you can start to see that actually is blue and then if we go positive, those start to go lighter, instead of darker.
All right, so a whole bunch of different colors you can use and the advantage to using the theme colors rather than RGB, it is that as you change the theme back here on the page layout.
So, we'll just choose another random theme, those colors automatically change.
So, it picks up the different colors in the theme, very slick.
I think it's a cool that we have many more options on what they give us here, back in the home tab of the ribbon.
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
Excel VBA, chapter 17.
Tint and Shade.
Okay, so chapter 17 is all about sparklines.
Why am I not doing spark lines in the podcast?
Because I love, when I discover something new and cool and I was looking at colors, you know we always used to use, while color index back in Excel 2003 from one to 56, or you know, RGB.
You know, but now the macro recorder starts doing this, the theme color all right, you know in the themes I get it page layout, you can choose a theme and each theme has six colors.
You can see, how the colors change back there.
All right, and we can even define it a new theme, each theme is comprised of six accent colors and then we see these a lot, back on the home tab and either the font color or the fill color.
The six accent colors appear here.
But what's interesting is this lighter eighty percent, lighter sixty percent, lighter forty percent.
Darker twenty five percent, darker fifty percent.
So, I think about those six accent colors are shown with five different variations there.
Now, I love when I discover the VBA does something better than the Excel interface does and that was exactly, what happened here because when I actually looked at this code, we have theme color, XL theme color accent 1 through 6.
Those are the six for the theme.
But then tint shade turns out that it can be a number anywhere from negative .99, all the way up to positive .99.
The negative numbers are darker, the lighter numbers are the positive numbers are lighter and so I actually wrote a little macro here that went through and looked at the hundred different darker values.
So, this is the orange, that is our theme color 6 but it goes all the way down to black, depending on what tint and shade we use and I'll scroll down here.
Here's a another little macro I wrote that, came up with the 99 lighter colors.
All right, so that one orange color goes from completely black to completely white in 200 different steps.
So, using tint in shades to modify the theme accent color.
You're not limited just those five, that they happen to give us.
Use and again, positive and negative members here.
Let's try, it will choose a cell over here now, in this case theme accent color 1 is blue.
So, we'll just use a tint and shade of zero.
First to see how that looks, we'll run that code and you see we get that blue, all right.
But then if we would use a tint shade, let's go darker, so minus point 75.
Run that!
Alright, so oh! That's almost black, almost too dark.
So, how about minus .5.
That one.
Okay, so you can start to see that actually is blue and then if we go positive, those start to go lighter, instead of darker.
All right, so a whole bunch of different colors you can use and the advantage to using the theme colors rather than RGB, it is that as you change the theme back here on the page layout.
So, we'll just choose another random theme, those colors automatically change.
So, it picks up the different colors in the theme, very slick.
I think it's a cool that we have many more options on what they give us here, back in the home tab of the ribbon.
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
