Ahmed is looking for a formula that will return certain text if another cell has been crossed out. This is not possible with the Excel built-in functions. But it is possible using a User Defined Function in VBA. Today's episode shows you how.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1917.
Formula to Detect Cross out (or any Excel formatting).
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Back in episode 1538, I showed how to use Conditional Formatting to add diagonal borders, to make it appear that we've crossed out a cell.
And Ahmed just posted a question.
And Ahmed just posted a question in response to that video, where he wants to go the opposite way.
So, if he would cross out a cell, like for example A1, then another cell, cell B1 would change the message to say: “Target accomplished!”.
So, the question: is there any kind of a function that we can use, like =CROSSEDOUT or =ISCROSSEDOUT, to detect the formula in another cell?
No, there's not.
And so I went back to Ahmed and I said: the only way we're going to be able to do this, is with some VBA.
And even then it's not going to be perfect.
He says: hey, VBA is fine, I've never done it, so walk me through it.
Alright, so here, couple of things.
Podcast1917.xlsx.
This is the default file format.
This is a horrible file format, because it's the only file format in the entire world, that does not allow macros.
So first thing, File, Save As, we're going to Save As to the exact same spot, but we're going to change the Save As type from xlsx to anything else: xlsm, xlsb, even xls.
Why they make this be the default, it is so horribly frustrating.
If you do not do that, this process will not work, so don't skip that step.
Next, we have to change your macro security.
The way that I do this is: Alt+T (for Tom), so you're right here: we get T, and then M (for Mary), and then S (for Sam).
That will take us to the Trust Center.
There is another way to get here, it is horribly long.
By default you have “Disable all macros with notification”.
That means that macros will not run in your Excel.
Change it into this one, so that way the macros will run, all right.
Those two steps are very important.
Now we're going to write our own function in VBA, so you're going to press Alt+F11.
If never used VBA before, you're going to have just big ugly screen.
Come here to View, Project Explorer, or press Ctrl+R and then find your file.
Here's my file.
Then I click in that file and say Insert, a Module.
All right, now this is where we can write our custom function.
I'm not going to make… I'm not going to ask you to sit there and watch me type everything, cause I’m a slow typer.
I had this already on the clipboard.
So, we start with the word “Function”.
That tells Excel that this is a custom function, it's going to return a value to the spreadsheet.
The name of the function is “IsCrossedOut” and we're passing it a cell.
So MyCell is the variable name, that I'm going to use in this function, to figure out what's going on with that cell.
I turn on Application.Volatile, but that's still, I'll explain this later, is not going to make our life easier.
If you're watching this video and you need to detect bold or italic, or underlined, you would use these lines of code instead.
But we're looking for cross out.
So, with a cross out, it's one of two things.
Either they did the xlDiagonalDown, or the xlDiagonalUp.
One of those two, or maybe they just use Strikethrough (Ctrl+5), all right.
So these three things are checking, to see if the cell that we are referring to in this formula, has the DiagonalDown, the DiagonalUp or Strikethrough.
The Or operator out here combines all three of these.
This _ is a continuation character, don't forget the space before the underscore, it will not work, if you don't type the space before the underscore.
All right, so if any of these things are true, then the function named IsCrossedOut is going to return the word “Target Accomplished!”.
Otherwise, it's going to return nothing: “ ”. And then If to end that little section there, and then End Function.
Alright, so we come back to our spreadsheet and we use =IsCrossedOut(A1) of this cell, and because that cell is not crossed out, we should get nothing.
But when I copy that formula down to all of these other ones, it returns “Target Accomplished!”.
Isn't that amazing?
We just added a brand new function to excel, that is actually looking at formatting.
Something that none of Excel’s functions currently do.
Now, here is the one horribly frustrating thing about Excel.
If we type in the number 123 and we press enter, they recalculate the entire worksheet.
But if we go to that 123 and we do any formatting, Ctrl+B, Ctrl+U, Ctrl+I, Ctrl+5 to strike through, that does not cause Excel to calculate the worksheet.
So that means, that these functions that we wrote up here, even though I said Application.Volatile, will not recalculate, if the only change we made, is a formatting change.
So I'm going to come here and do Ctrl+5 to cross that out, and you see, that the function does not update.
Now, in order to get the function update, we either have to press the F9 key to force the calculation, or we just have to come to any other cell and enter a number, which will cause everything to refresh.
That's a little annoying, that we have to do that.
But if you understand, you know, the logic behind it, we need to be able to format a spreadsheet without having a recalculation happen.
Just imagine, if you had a spreadsheet that took 40 seconds to recalculate, if hadn't used fast Excel from Charles Williams to make it faster, and then every time you did bold or underline, it would take 40 seconds, that would be horrible.
So they let us to do all the formatting changes without running the calculation.
When you have crossed everything out and you need to calculate, just press the F9 key.
And you'll be good to go.
All right, so I'm going to save this file, and then File, Close, and then when I reopen that file the, next time you open this file, you should get either this security notice, or a security notice right here above the spreadsheet, and you have to choose Enable Macros.
That was our macro security setting, that says we're going to disable the macros, but with notification that allows you to turn the macros back on.
You know, we wrote this macro, so therefore you know that it's safe and you would enable macros.
But if you get, you know, emails from your competitor and it has a macro, and then maybe don't enable the macros, alright.
So that's the whole idea there.
All right, so I want to thank Ahmed for sending that question in.
This obviously can be modified to handle any kind of… any kind of formatting.
And the trick is going to be for you, to figure out what formatting you're checking for.
Let's just say, that you wanted to check, to see if the cell had been colored a certain shade of red.
You know, for example we wanted to find any cells that had that red fill.
In order to get that code, here's what I would do.
I would go to the VIEW tab, I would go to Macros and Record Macro.
And you can just call it Macro3, but I'm going to say HowToColorRed.
And I'm never going to run this macro, I’m just going to use the macro recorder to learn what the code is.
And then come back here, the HOME tab and choose the red that I'm looking for, and then down here, next to READY, “stop recording”, Alt+F8 to get a list of my macros and where was it?
It was HowToColorRed.
I click Edit and I see that they did Selection.Interior, .Color = 255.
This With is saying that we're doing Selection.Interior for each of these.
The .Color = 255 is a really important one, so: .Color = 255.
So my code, the other one, would become If Selection.Interior.Color = 255 then.
Alright, so I’m going to just copy that, Ctrl+C, come back to my Module here, and paste that in, alright.
So now, this macro, I'm going to actually just comma that out, this macro becomes now something that's checking for a fill color of 255.
So no matter what kind of formatting you're looking for, by using that recorded macro trick, you can at least figure out, you know, what the code is.
I’m actually going to throw this one away.
It’s just… it was just a little trick to figure out what code we needed to use to put in that function right there.
All right, so an interesting question from Ahmed, this will sort of work, provided you're willing to press the F9 key.
All right, hey, I want to thank everyone for stopping by, I want to thank Ahmed for sending that question in, see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1917.
Formula to Detect Cross out (or any Excel formatting).
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Back in episode 1538, I showed how to use Conditional Formatting to add diagonal borders, to make it appear that we've crossed out a cell.
And Ahmed just posted a question.
And Ahmed just posted a question in response to that video, where he wants to go the opposite way.
So, if he would cross out a cell, like for example A1, then another cell, cell B1 would change the message to say: “Target accomplished!”.
So, the question: is there any kind of a function that we can use, like =CROSSEDOUT or =ISCROSSEDOUT, to detect the formula in another cell?
No, there's not.
And so I went back to Ahmed and I said: the only way we're going to be able to do this, is with some VBA.
And even then it's not going to be perfect.
He says: hey, VBA is fine, I've never done it, so walk me through it.
Alright, so here, couple of things.
Podcast1917.xlsx.
This is the default file format.
This is a horrible file format, because it's the only file format in the entire world, that does not allow macros.
So first thing, File, Save As, we're going to Save As to the exact same spot, but we're going to change the Save As type from xlsx to anything else: xlsm, xlsb, even xls.
Why they make this be the default, it is so horribly frustrating.
If you do not do that, this process will not work, so don't skip that step.
Next, we have to change your macro security.
The way that I do this is: Alt+T (for Tom), so you're right here: we get T, and then M (for Mary), and then S (for Sam).
That will take us to the Trust Center.
There is another way to get here, it is horribly long.
By default you have “Disable all macros with notification”.
That means that macros will not run in your Excel.
Change it into this one, so that way the macros will run, all right.
Those two steps are very important.
Now we're going to write our own function in VBA, so you're going to press Alt+F11.
If never used VBA before, you're going to have just big ugly screen.
Come here to View, Project Explorer, or press Ctrl+R and then find your file.
Here's my file.
Then I click in that file and say Insert, a Module.
All right, now this is where we can write our custom function.
I'm not going to make… I'm not going to ask you to sit there and watch me type everything, cause I’m a slow typer.
I had this already on the clipboard.
So, we start with the word “Function”.
That tells Excel that this is a custom function, it's going to return a value to the spreadsheet.
The name of the function is “IsCrossedOut” and we're passing it a cell.
So MyCell is the variable name, that I'm going to use in this function, to figure out what's going on with that cell.
I turn on Application.Volatile, but that's still, I'll explain this later, is not going to make our life easier.
If you're watching this video and you need to detect bold or italic, or underlined, you would use these lines of code instead.
But we're looking for cross out.
So, with a cross out, it's one of two things.
Either they did the xlDiagonalDown, or the xlDiagonalUp.
One of those two, or maybe they just use Strikethrough (Ctrl+5), all right.
So these three things are checking, to see if the cell that we are referring to in this formula, has the DiagonalDown, the DiagonalUp or Strikethrough.
The Or operator out here combines all three of these.
This _ is a continuation character, don't forget the space before the underscore, it will not work, if you don't type the space before the underscore.
All right, so if any of these things are true, then the function named IsCrossedOut is going to return the word “Target Accomplished!”.
Otherwise, it's going to return nothing: “ ”. And then If to end that little section there, and then End Function.
Alright, so we come back to our spreadsheet and we use =IsCrossedOut(A1) of this cell, and because that cell is not crossed out, we should get nothing.
But when I copy that formula down to all of these other ones, it returns “Target Accomplished!”.
Isn't that amazing?
We just added a brand new function to excel, that is actually looking at formatting.
Something that none of Excel’s functions currently do.
Now, here is the one horribly frustrating thing about Excel.
If we type in the number 123 and we press enter, they recalculate the entire worksheet.
But if we go to that 123 and we do any formatting, Ctrl+B, Ctrl+U, Ctrl+I, Ctrl+5 to strike through, that does not cause Excel to calculate the worksheet.
So that means, that these functions that we wrote up here, even though I said Application.Volatile, will not recalculate, if the only change we made, is a formatting change.
So I'm going to come here and do Ctrl+5 to cross that out, and you see, that the function does not update.
Now, in order to get the function update, we either have to press the F9 key to force the calculation, or we just have to come to any other cell and enter a number, which will cause everything to refresh.
That's a little annoying, that we have to do that.
But if you understand, you know, the logic behind it, we need to be able to format a spreadsheet without having a recalculation happen.
Just imagine, if you had a spreadsheet that took 40 seconds to recalculate, if hadn't used fast Excel from Charles Williams to make it faster, and then every time you did bold or underline, it would take 40 seconds, that would be horrible.
So they let us to do all the formatting changes without running the calculation.
When you have crossed everything out and you need to calculate, just press the F9 key.
And you'll be good to go.
All right, so I'm going to save this file, and then File, Close, and then when I reopen that file the, next time you open this file, you should get either this security notice, or a security notice right here above the spreadsheet, and you have to choose Enable Macros.
That was our macro security setting, that says we're going to disable the macros, but with notification that allows you to turn the macros back on.
You know, we wrote this macro, so therefore you know that it's safe and you would enable macros.
But if you get, you know, emails from your competitor and it has a macro, and then maybe don't enable the macros, alright.
So that's the whole idea there.
All right, so I want to thank Ahmed for sending that question in.
This obviously can be modified to handle any kind of… any kind of formatting.
And the trick is going to be for you, to figure out what formatting you're checking for.
Let's just say, that you wanted to check, to see if the cell had been colored a certain shade of red.
You know, for example we wanted to find any cells that had that red fill.
In order to get that code, here's what I would do.
I would go to the VIEW tab, I would go to Macros and Record Macro.
And you can just call it Macro3, but I'm going to say HowToColorRed.
And I'm never going to run this macro, I’m just going to use the macro recorder to learn what the code is.
And then come back here, the HOME tab and choose the red that I'm looking for, and then down here, next to READY, “stop recording”, Alt+F8 to get a list of my macros and where was it?
It was HowToColorRed.
I click Edit and I see that they did Selection.Interior, .Color = 255.
This With is saying that we're doing Selection.Interior for each of these.
The .Color = 255 is a really important one, so: .Color = 255.
So my code, the other one, would become If Selection.Interior.Color = 255 then.
Alright, so I’m going to just copy that, Ctrl+C, come back to my Module here, and paste that in, alright.
So now, this macro, I'm going to actually just comma that out, this macro becomes now something that's checking for a fill color of 255.
So no matter what kind of formatting you're looking for, by using that recorded macro trick, you can at least figure out, you know, what the code is.
I’m actually going to throw this one away.
It’s just… it was just a little trick to figure out what code we needed to use to put in that function right there.
All right, so an interesting question from Ahmed, this will sort of work, provided you're willing to press the F9 key.
All right, hey, I want to thank everyone for stopping by, I want to thank Ahmed for sending that question in, see you next time for another netcast – MrExcel.
