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.

Sub BorderAround()

    Dim Cell    As Range
    Dim Rng     As Range
    Dim Row     As Long
    Dim Start   As String
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Cell = Wks.Cells.Find("Packing Slip", , xlValues, xlWhole, xlByRows, xlNext, False, False, False)
        
        If Cell Is Nothing Then Exit Sub
        
            Start = Cell.Address
            
            Do
                Set Rng = Cell.CurrentRegion
                Set Cell = Wks.Cells.FindNext(Cell)
                    For Row = 4 To Rng.Rows.Count
                        If Rng.Cells(Row, "D") > 0 Then
                            Rng.Rows(Row).BorderAround xlContinuous, xlThin
                        End If
                    Next Row
                If Cell Is Nothing Then Exit Do
                If Cell.Address = Start Then Exit Do
            Loop
        
End Sub

**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?

Dim Row     As Long

I appreciate your help!
Thanks!
Klotzy

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

FWIW:

Sub klotzy()
Dim rcell As Range
For Each rcell In Range("D2:D" & Range("D" & Rows.Count).End(3).row)
    If IsNumeric(rcell) And Len(rcell) > 0 Then
        With Range(Cells(rcell.row, "A"), Cells(rcell.row, "D"))
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
             End With
            .Borders(xlInsideVertical).LineStyle = xlNone
        End With
    End If
Next rcell
End Sub

**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

Hybrid View

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