Mark this date: October 18th, 2011 - Bill "MrExcel" Jelen's first Podcast for MAC Users!
Joe has Excel 2011 for Mac. He turned on the macro recorder and changed some colors in his worksheet. The Excel Mac Macro Recorder uses 'xlAutomatic' for all colors instead of recording the actual color. In this, my first-ever podcast directly focusing on Excel for Mac issues and those using Excel for the Mac, I will show you how to use the 'RGB Function' to fix you macro.
Using Excel on your MAC? Bill wants to know. Drop him a note at * bill @ mrexcel.com * and tell him what version of Excel for Mac you use. As Bill mentioned in today's podcast, "...maybe it's time".
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Joe has Excel 2011 for Mac. He turned on the macro recorder and changed some colors in his worksheet. The Excel Mac Macro Recorder uses 'xlAutomatic' for all colors instead of recording the actual color. In this, my first-ever podcast directly focusing on Excel for Mac issues and those using Excel for the Mac, I will show you how to use the 'RGB Function' to fix you macro.
Using Excel on your MAC? Bill wants to know. Drop him a note at * bill @ mrexcel.com * and tell him what version of Excel for Mac you use. As Bill mentioned in today's podcast, "...maybe it's time".
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1451 – MAC xlautomatic This is crazy! I got this question twice in the last week.
Joe is saying, “You know, you need to get a Mac.” Right, he likes the way that I teach.
He says, “You’re doing really well with the Mac audience.” Here is a question.
If you’re using a Mac, shoot me a not.
Bill@MrExcel.com.
I will do a little survey and see how many viewers out there are actually watching this on a Mac.
Maybe it’s time.
I don’t know.
So here's in Excel for the Mac 2011.
There's a bug with the macro recorded that if you choose the color while the macro recorder is recording, it just records that as xlAutomatic.
xlAutomatic, we’ll take a look at the code.
This is the real code that the Mac would do, but it comes out something like this.
Font.Color=xlAutomatic.
Interior.Color=xlAutomatic.
And I've had two people ask me about this in the last week, so that means that there are actually two people in the world using Excel 2011.
There’s probably more.
There's probably 7 or 10.
Who knows?
Let's take a look at how we could actually solve this.
So I'm going to select the cell that I used during the macro recording, and I'm going to go to the Paint bucket.
I'm going to open the Paint bucket, and go to More Colors.
Once I’m in More Colors, I'm going to come back here to the Custom tab and I'm going to grab a sheet of paper.
This is real low-tech here.
And I'm going to write down those numbers – red 54, green 96 and blue 146.
And then I'm going to come here to the same cell.
Go to the Font tab, More Colors again, back to Custom, and that was easy.
That's just a straight 255, 0, 0.
All right.
Now once we have those bits of information, we can go to the macro editor, Alt+F11, and where it has xlAutomatic, we're going to put in a great function called RGB.
RGB stands for red, green, blue.
And see, it's asking for three numbers.
Red As Integer, Green As Integer, Blue As Integer.
So I put in...
That's the font color.
It was just...
That's easy.
That's just Selection.FontColor=RGB(255,0,0).
But then for the Interior where we used a weird blue, that is, Selection.InteriorColor=RGB(54,96,146).
And that should do it.
So now, let's try and run this macro here on just another cell.
Alt+F8, Macro1, Run, and we get the exact same colors that we used in the original setting.
That's a weird book, right, as strange as the macro recorder.
Well actually, I guess it's not weird at all because the VBA macro recorder went away for Excel 2008.
It’s brand new back in Excel 2011.
So they're kind of starting from scratch.
They're on the Mac team with a macro recorder.
So I guess it's not that weird of a bug that it’s not recording the color, but easy enough to use the RGB function.
And again, to get the numbers for the RGB function, just choose the cell that has the right colors.
Go to More Colors, go to Custom, and write down the RGB, all right.
There you have it.
Thanks to Joe for sending that question in.
Thanks to you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1451 – MAC xlautomatic This is crazy! I got this question twice in the last week.
Joe is saying, “You know, you need to get a Mac.” Right, he likes the way that I teach.
He says, “You’re doing really well with the Mac audience.” Here is a question.
If you’re using a Mac, shoot me a not.
Bill@MrExcel.com.
I will do a little survey and see how many viewers out there are actually watching this on a Mac.
Maybe it’s time.
I don’t know.
So here's in Excel for the Mac 2011.
There's a bug with the macro recorded that if you choose the color while the macro recorder is recording, it just records that as xlAutomatic.
xlAutomatic, we’ll take a look at the code.
This is the real code that the Mac would do, but it comes out something like this.
Font.Color=xlAutomatic.
Interior.Color=xlAutomatic.
And I've had two people ask me about this in the last week, so that means that there are actually two people in the world using Excel 2011.
There’s probably more.
There's probably 7 or 10.
Who knows?
Let's take a look at how we could actually solve this.
So I'm going to select the cell that I used during the macro recording, and I'm going to go to the Paint bucket.
I'm going to open the Paint bucket, and go to More Colors.
Once I’m in More Colors, I'm going to come back here to the Custom tab and I'm going to grab a sheet of paper.
This is real low-tech here.
And I'm going to write down those numbers – red 54, green 96 and blue 146.
And then I'm going to come here to the same cell.
Go to the Font tab, More Colors again, back to Custom, and that was easy.
That's just a straight 255, 0, 0.
All right.
Now once we have those bits of information, we can go to the macro editor, Alt+F11, and where it has xlAutomatic, we're going to put in a great function called RGB.
RGB stands for red, green, blue.
And see, it's asking for three numbers.
Red As Integer, Green As Integer, Blue As Integer.
So I put in...
That's the font color.
It was just...
That's easy.
That's just Selection.FontColor=RGB(255,0,0).
But then for the Interior where we used a weird blue, that is, Selection.InteriorColor=RGB(54,96,146).
And that should do it.
So now, let's try and run this macro here on just another cell.
Alt+F8, Macro1, Run, and we get the exact same colors that we used in the original setting.
That's a weird book, right, as strange as the macro recorder.
Well actually, I guess it's not weird at all because the VBA macro recorder went away for Excel 2008.
It’s brand new back in Excel 2011.
So they're kind of starting from scratch.
They're on the Mac team with a macro recorder.
So I guess it's not that weird of a bug that it’s not recording the color, but easy enough to use the RGB function.
And again, to get the numbers for the RGB function, just choose the cell that has the right colors.
Go to More Colors, go to Custom, and write down the RGB, all right.
There you have it.
Thanks to Joe for sending that question in.
Thanks to you for stopping by.
I’ll see you next time for another netcast from MrExcel.
