If # > 0 in $D4 then $A4 thru $D4 BorderAround

**klotzy** · 10-25-2015, 10:14 PM

Hi!
Could you help me write a code to add borders around information in Cells A-D.
The workbook I'm attaching has the top example without the borders and the bottom example with the borders for a visual.
Please note, there will always be a number in column D if that row needs borders.

borders.xlsx

If conditional formatting would work better for this, please let me know. I can't figure out if conditional formatting can do outside borders around more than one cell on a row.

Thanks in advance!
Klotzy

**FDibbins** · 10-25-2015, 10:19 PM

Regular CF will work just fine for this, no VBA needed.

1. highlight the range you want to apply the conditional formatting to (A4:E5 in your sample)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$D4>0
Format Border Outline

**klotzy** · 10-25-2015, 10:34 PM

Thanks FDibbins for the quick reply.
Unfortunately, I can't have each cell bordered, only the outline. For example, the vertical border line between cells A and B can't be there.

I'm hoping to select an entire sheet full of these Packing Lists and have A:D Bordered only when there is a number in column D. The Packing List header will not get a border.

Do you have more advice?
Thanks again,
Klotzy

**FDibbins** · 10-25-2015, 10:44 PM

OK, that just means a few more rules.

Use what I gave you as the 1st rule (across the entire range), but change the border to just top and bottom - you will need to "unclick" the left and right borders
Then create a new rule that ONLY applies to the A4:A5 range, use the exact same formula and under Format/Border, click the LEFT border
Then repeat that for the right border

**Leith Ross** · 10-25-2015, 11:54 PM

Hello klotzy,

Welcome to the Forum!
I don't think conditional formatting is a good option for you. If you will have many packing slips per page and they will go across as well as down the sheet then conditional formatting would not be a good choice.

Here is a VBA macro that will find each packing slip on the sheet and add the borders to only the cells that need them. This has been added to the attached workbook. There is button on the sheet to run the macro.

Please Login or Register  to view this content.

**klotzy** · 10-26-2015, 10:44 PM

Thanks FDibbins for your help - I appreciate the tips.
Leith Ross - I agree that VBA will be more fitting to my needs, thanks very much for the code.
Can you please tell me how to alter the border to only border around A:D instead of A:the last used cell in the row?
Does it do that because you declared Row as Long?

Please Login or Register  to view this content.

I appreciate your help!
Thanks!
Klotzy

**JOHN H. DAVIS** · 10-27-2015, 07:25 AM

FWIW:

Please Login or Register  to view this content.

**klotzy** · 10-27-2015, 10:12 AM

Thank you so much John H Davis! That's perfect!

You all are the best!

**JOHN H. DAVIS** · 10-27-2015, 10:43 AM

You're welcome. Glad to help out and thanks for the feedback.

If # > 0 in $D4 then $A4 thru $D4 BorderAround

LinkBack

Thread Tools

Rate This Thread

Display

If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Re: If # > 0 in $D4 then $A4 thru $D4 BorderAround

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] BorderAround method failure

Bookmarks

Bookmarks

Posting Permissions