VBA: Apply border if there is something in the cell?

[CC268]

Hey guys,

I have a long spreadsheet with tons of data in all sorts of cells. I just simply want to add a border to anything that has a value (whether it is a number, text, whatever) in the cell. I feel like this should be a fairly simple to do?

Thanks!

 

[CC268]

I believe I can actually just use conditional formatting for this...currently trying to figure that out.

 

[Eric W]

Sure!

1) Select the cells you want to format, you can just click the little triangle between the A and 1 to select the whole sheet.
2) Click Conditional Formatting --> New Rule --> Use a formula
3) Type this formula:
=LEN(A1)>0
where A1 is the upper left corner of your selected range
4) Click Format --> Border, and choose a border.
5) Click OK

That should do it. Let me know how it works.

 

[CC268]

Sure!

1) Select the cells you want to format, you can just click the little triangle between the A and 1 to select the whole sheet.
2) Click Conditional Formatting --> New Rule --> Use a formula
3) Type this formula:
=LEN(A1)>0
where A1 is the upper left corner of your selected range
4) Click Format --> Border, and choose a border.
5) Click OK

That should do it. Let me know how it works.

Hmm..that arbitrarily added borders all over the place...some with text, but a lot with no text in them too...not sure why. Maybe there was some formatting previously applied that needs to be cleared? I guess I could try a new clean sheet and see if it works.

 

[CC268]

It worked! I must have had some weird formatting going on...I just copied it over to a new sheet and it worked perfectly! Thanks! I will have to look up that LEN function so I know what it actually going on there lol

 

[Eric W]

The LEN function just returns the length of the cell. If you got a lot of cells being formatted that looked empty, then they probably had some spaces or non printable characters in them, which is why going to a new sheet worked. You can change the formula to

=LEN(TRIM(A1))>0

to handle spaces. You can add the CLEAN function if you think there's non-printable characters. It's also worth noting that this works on the value of the cell. If there's a formula that returns "", then it won't be formatted.

Glad to help!

 

[CC268]

Interesting...so much to learn...a bit overwhelming for a complete newbie like me - but I will keep trying stuff out! Thanks!