From Jumping Bean on YouTube, the question: "How can I apply Conditional Formatting to Double the Diagonal Borders when the Value is a Number?" In Episode #1538, Bill offers a method to 'cross out' only Numeric Values in each cell.
...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"
...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"
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1538: “Conditional diagonal borders.” Well, hey everyone.
Welcome back, I hope you enjoyed a VLOOKUP week back from Florida and here for the regular podcasts. This sent in by jumping beans at YouTube.
I want to apply conditional formatting double diagonal borders when it is a number.
All right so let's just, let's, let's take a look at this.
We're going to go to the Conditional Formatting, New Rule and we're going to use a formula to determine which cells to formula format =ISNUMBER(.
I'm in C3, all right and when it's the number we can apply some sort of formatting.
For example, we can use the strikethrough click OK, click OK, all right and then if it's numeric it struck through, if it's anything else ABC, then it's not struck through.
Alright, so that works but that's not what jumping beans us try to do.
Jumping beans is trying to apply diagonal borders that look like this, Ctrl 1 will go to border and use this diagonal and this diagonal.
Alright, that's the goal.
Now here's the problem.
We go into Conditional Formatting and say New Rule.
Use a form to determine which cells to format =ISNUMBER.
Now we're in C5 format, there is a Border Tab and I can change the borders but look these are grayed out, they are grayed out, and I can't even, like, clear the border.
So first thing I try to do is I try to put the diagonal borders in and then say okay, let's format it as No Borders and it won't override those diagonal borders.
Like, how this is this is bad it's about ready to say that it couldn't be done and I said okay, wait wait wait, let's try this and this seems like a little bit of a sneaky trick.
We're going to go here, to this cell and we're always going to apply the diagonal borders but I'm not going to apply it in black.
I'm going to use some other color and I'm going to use something that were going to be able to see.
But you know isn't that obtrusive, so I'll go with the red here.
Alright, so there's our diagonal borders, click OK.
Now what I'm going to do all OD for Conditional Formatting, New Rule, use a formula =NOT(ISNUMBER.
This time I'm in C7 format and since I can't do anything about the borders we're going to do is I'm going to use a Fill.
I'm going to use the same Fill as the color of the diagonal cross out borders.
Alright, so what we get then.
So now we have a number and it looks like the, it's crossed out.
We have text, it's not crossed out of a word but of course we have the Fill.
But you know if you're just trying to get that cross the diagonal borders to cross out the cell then maybe this is a horrible cheat that would actually work and convince your manager that it worked.
We'll send them off to jumping beans.
See, if it is acceptable or not acceptable, strange that then Microsoft lets us do all the other borders but not those diagonal borders.
There must be some history there.
I even tried to Macro, I wrote the Macro and said okay, let's do the technical border instead of the top border and as soon as it got to that lied, it just no deals.
So it’s not going to work.
He is a minor if a friend or co-worker is asking for a great book about Excel suggest this book “Learn Excel 2007 through 2010” for MrExcel.
All the ideas in this book came from the videos at YouTube.
It's like watching three years worth of YouTube videos all in the palm of your hand.
I want to thankyou for stopping by.
See you next time for another netcast, MrExcel.
Learn Excel from MrExcel podcast, episode 1538: “Conditional diagonal borders.” Well, hey everyone.
Welcome back, I hope you enjoyed a VLOOKUP week back from Florida and here for the regular podcasts. This sent in by jumping beans at YouTube.
I want to apply conditional formatting double diagonal borders when it is a number.
All right so let's just, let's, let's take a look at this.
We're going to go to the Conditional Formatting, New Rule and we're going to use a formula to determine which cells to formula format =ISNUMBER(.
I'm in C3, all right and when it's the number we can apply some sort of formatting.
For example, we can use the strikethrough click OK, click OK, all right and then if it's numeric it struck through, if it's anything else ABC, then it's not struck through.
Alright, so that works but that's not what jumping beans us try to do.
Jumping beans is trying to apply diagonal borders that look like this, Ctrl 1 will go to border and use this diagonal and this diagonal.
Alright, that's the goal.
Now here's the problem.
We go into Conditional Formatting and say New Rule.
Use a form to determine which cells to format =ISNUMBER.
Now we're in C5 format, there is a Border Tab and I can change the borders but look these are grayed out, they are grayed out, and I can't even, like, clear the border.
So first thing I try to do is I try to put the diagonal borders in and then say okay, let's format it as No Borders and it won't override those diagonal borders.
Like, how this is this is bad it's about ready to say that it couldn't be done and I said okay, wait wait wait, let's try this and this seems like a little bit of a sneaky trick.
We're going to go here, to this cell and we're always going to apply the diagonal borders but I'm not going to apply it in black.
I'm going to use some other color and I'm going to use something that were going to be able to see.
But you know isn't that obtrusive, so I'll go with the red here.
Alright, so there's our diagonal borders, click OK.
Now what I'm going to do all OD for Conditional Formatting, New Rule, use a formula =NOT(ISNUMBER.
This time I'm in C7 format and since I can't do anything about the borders we're going to do is I'm going to use a Fill.
I'm going to use the same Fill as the color of the diagonal cross out borders.
Alright, so what we get then.
So now we have a number and it looks like the, it's crossed out.
We have text, it's not crossed out of a word but of course we have the Fill.
But you know if you're just trying to get that cross the diagonal borders to cross out the cell then maybe this is a horrible cheat that would actually work and convince your manager that it worked.
We'll send them off to jumping beans.
See, if it is acceptable or not acceptable, strange that then Microsoft lets us do all the other borders but not those diagonal borders.
There must be some history there.
I even tried to Macro, I wrote the Macro and said okay, let's do the technical border instead of the top border and as soon as it got to that lied, it just no deals.
So it’s not going to work.
He is a minor if a friend or co-worker is asking for a great book about Excel suggest this book “Learn Excel 2007 through 2010” for MrExcel.
All the ideas in this book came from the videos at YouTube.
It's like watching three years worth of YouTube videos all in the palm of your hand.
I want to thankyou for stopping by.
See you next time for another netcast, MrExcel.
