Today we look at changing font color automatically after editing a cell. We know that we could use F4, the Format Painter or a Excel VBA [Visual Basic for Applications] Macro - Using Excel VBA create a macro to add a red font color automatically after editing a cell. In Episode #1392, Bill shows us all three of these techniques for formatting our data.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1392.
Format Changed Cells.
Well, Hey! Welcome back to the MrExcel netcast.
I got a note from Tom.
Tom says, he's been watching since episode 103.
That's a long time.
He just got a large amount of data came down from an inventory or something like that.
Needs to go through and quickly, quickly format cells.
Now, you know my first thought here is, he was looking for error cells.
You know, if you apply a format to a cell, alright!
And you're not doing anything else to the data, the [ F4 ] key is just an amazing trick.
You can just go select a cell, press [ F4 ] and it repeats the last command.
Whatever, the last command you did, it will repeat that.
So, if you just need to mark a whole bunch of sales, well, mark one and then you can use [ F4 ].
But let's say that you did two things, so let's say you applied a background color and also a foreground color.
Now, when you press [ F4 ] and fortunately both of those things aren't going to get repeated, just the last one.
So, that's where the format painter can be an amazing trick, a little paintbrush up here double click.
All right, and now any time, I touch a cell.
I'm applying that format.
So, I talked to Tom but really what Tom is doing is when he spots an error.
Where the data on the server was wrong and edits that cell he wants to automatically change the color of that cell and to do that, that's going to be a little bit of VBA.
Let me press [ escape ] to get out of format painter mode here, and then we'll do [ alt F11 ].
To switch over and I'm going to find the worksheet I'm in.
So, I'm in Book 1, I'm on sheet 1.
I want to double click there and now we have two drop downs at the top from the left most drop down.
We want to choose worksheet and then change.
Every time that we change a cell, this little macro will get run.
It gives us a variable called Target.
Target is the range that we just changed.
So, right in here, we just say Target dot font dot color I'm gonna use color index, equals 4.
If you're, this will work in Excel 2003 by the way, if you're in Excel 2007, you just do dot color equals 255.
255 being read, and I'm not actually sure I think 4 is red.
But we'll find out here real quick, when we go back to Excel.
So, I just come here and change the number thousand then and see a green, that's kind of ugly it must be red.
So 3 is 2, would have thought, I would have tested this before.
I turned on the recorder, which other half...
Yeah! There we go. So, dot color Index equals 3 or if you're in Excel 2007, newer windows, dot color equals 255, it'll get at it nice red.
If you want some other color, turn on the macro recorder and figure out what the settings are there.
So, this kind of just runs quietly in the background to change the cell and it will instantly...
Not as cool as outlook of course, when you start to change things at out look, automatically changes to blue or you know, so...
Requires macro still a cool, cool way to go.
Wanna thank Tom for hanging around since episode 103.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1392.
Format Changed Cells.
Well, Hey! Welcome back to the MrExcel netcast.
I got a note from Tom.
Tom says, he's been watching since episode 103.
That's a long time.
He just got a large amount of data came down from an inventory or something like that.
Needs to go through and quickly, quickly format cells.
Now, you know my first thought here is, he was looking for error cells.
You know, if you apply a format to a cell, alright!
And you're not doing anything else to the data, the [ F4 ] key is just an amazing trick.
You can just go select a cell, press [ F4 ] and it repeats the last command.
Whatever, the last command you did, it will repeat that.
So, if you just need to mark a whole bunch of sales, well, mark one and then you can use [ F4 ].
But let's say that you did two things, so let's say you applied a background color and also a foreground color.
Now, when you press [ F4 ] and fortunately both of those things aren't going to get repeated, just the last one.
So, that's where the format painter can be an amazing trick, a little paintbrush up here double click.
All right, and now any time, I touch a cell.
I'm applying that format.
So, I talked to Tom but really what Tom is doing is when he spots an error.
Where the data on the server was wrong and edits that cell he wants to automatically change the color of that cell and to do that, that's going to be a little bit of VBA.
Let me press [ escape ] to get out of format painter mode here, and then we'll do [ alt F11 ].
To switch over and I'm going to find the worksheet I'm in.
So, I'm in Book 1, I'm on sheet 1.
I want to double click there and now we have two drop downs at the top from the left most drop down.
We want to choose worksheet and then change.
Every time that we change a cell, this little macro will get run.
It gives us a variable called Target.
Target is the range that we just changed.
So, right in here, we just say Target dot font dot color I'm gonna use color index, equals 4.
If you're, this will work in Excel 2003 by the way, if you're in Excel 2007, you just do dot color equals 255.
255 being read, and I'm not actually sure I think 4 is red.
But we'll find out here real quick, when we go back to Excel.
So, I just come here and change the number thousand then and see a green, that's kind of ugly it must be red.
So 3 is 2, would have thought, I would have tested this before.
I turned on the recorder, which other half...
Yeah! There we go. So, dot color Index equals 3 or if you're in Excel 2007, newer windows, dot color equals 255, it'll get at it nice red.
If you want some other color, turn on the macro recorder and figure out what the settings are there.
So, this kind of just runs quietly in the background to change the cell and it will instantly...
Not as cool as outlook of course, when you start to change things at out look, automatically changes to blue or you know, so...
Requires macro still a cool, cool way to go.
Wanna thank Tom for hanging around since episode 103.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
