Ricardo has a Spreadsheet where active projects include live Formulas. Once a project is complete, he converts the Formulas to Values. He wants a way to mark all of the Cells which are still live Formulas by changing the color of the Cell's Data. Today, in Episode #1351, Bill shows us how to use a UDF [User Defined Function] to accomplish the task - using one (1) line of VBA [Visual Basic for Applications] to keep the process dynamic and reflect future changes in the Data Set.
...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 all of your Microsoft Excel needs visit 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 all of your Microsoft Excel needs visit 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 # 1351 - Mark Formulas Hey Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Ricardo.
Ricardo actually suggests that this would be a good dueling podcast.
As far as I know there's only one way to do this.
So it's not going to be a duel at all.
Ricardo has series of numbers and it doesn't matter what the situation is but there's certain items that are live formulas and then others he marks as complete.
And once it's marked as complete, he wants to change the color of the cell. All right.
And so here's the thing. Maybe a Macro that would that would both convert the formulas to values and use font colors or like that.
But I want to use conditional formatting to do this.
The problem is conditional formatting can't detect if it's a formula or not.
and you know I realize we could use Go to Special Ctrl + G Special Select all of the Formulas. Click OK and then color those but that's a static process that doesn't, you know change if we then come along and change that formula to a value. All right.
So see that stays red.
So you know that method's not going to work.
So I want to do something a little bit special.
And this goes all the way back to one of my very first books.
MrExcel on excel and when I switch over to VBA.
I'm going to insert a module. Very small little bit of code here's commonly called function.
I'll just call it HF or HasFormula and I'll just call it MyCell and then we say.
HF = MyCell.HasFormula HasFormula is a property that applies to a cell.
If there's a formula. It's TRUE. Otherwise, it's FALSE. Alright.
So now we have a custom defined function. I'll switch back to excel and right here, we will do "Conditional Formatting" "New Rule" "Use a formula to determine which cell format" and I'll say =HF (A1 A1 on being the top left corner cell that I want to format and then format is color of red.
Click OK.
Click OK.
Oh look at that that all of the cells that have formulas are now red.
If I choose a few cells Ctrl + C or [ inaudiable 2:26-7 ] Make those not be formulas anymore it switches back to black.
So now I can tell which cells are still live cells and which cells have been converted to static values.
So unfortunately requires just a tiny amount of VBA which ofcourse means we have to save this as a .xlsm or .xls in the old version of excel in order to allow that Macro to run, but it'll solve Ricardo's problem.
Alright well hey I want to thank you for stopping by.
I want to thank Ricardo for sending that question in.
Will see you next time for another netcast from MrExcel.
Learn excel from MrExcel podcast. Episode # 1351 - Mark Formulas Hey Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Ricardo.
Ricardo actually suggests that this would be a good dueling podcast.
As far as I know there's only one way to do this.
So it's not going to be a duel at all.
Ricardo has series of numbers and it doesn't matter what the situation is but there's certain items that are live formulas and then others he marks as complete.
And once it's marked as complete, he wants to change the color of the cell. All right.
And so here's the thing. Maybe a Macro that would that would both convert the formulas to values and use font colors or like that.
But I want to use conditional formatting to do this.
The problem is conditional formatting can't detect if it's a formula or not.
and you know I realize we could use Go to Special Ctrl + G Special Select all of the Formulas. Click OK and then color those but that's a static process that doesn't, you know change if we then come along and change that formula to a value. All right.
So see that stays red.
So you know that method's not going to work.
So I want to do something a little bit special.
And this goes all the way back to one of my very first books.
MrExcel on excel and when I switch over to VBA.
I'm going to insert a module. Very small little bit of code here's commonly called function.
I'll just call it HF or HasFormula and I'll just call it MyCell and then we say.
HF = MyCell.HasFormula HasFormula is a property that applies to a cell.
If there's a formula. It's TRUE. Otherwise, it's FALSE. Alright.
So now we have a custom defined function. I'll switch back to excel and right here, we will do "Conditional Formatting" "New Rule" "Use a formula to determine which cell format" and I'll say =HF (A1 A1 on being the top left corner cell that I want to format and then format is color of red.
Click OK.
Click OK.
Oh look at that that all of the cells that have formulas are now red.
If I choose a few cells Ctrl + C or [ inaudiable 2:26-7 ] Make those not be formulas anymore it switches back to black.
So now I can tell which cells are still live cells and which cells have been converted to static values.
So unfortunately requires just a tiny amount of VBA which ofcourse means we have to save this as a .xlsm or .xls in the old version of excel in order to allow that Macro to run, but it'll solve Ricardo's problem.
Alright well hey I want to thank you for stopping by.
I want to thank Ricardo for sending that question in.
Will see you next time for another netcast from MrExcel.
