Set Inside Horizontal cell borders-CodePudding

I have a bit of VBA code that attempts to set the LEFT and TOP border on a bunch of non-contiguous cells - previously appended to a Range object based on some criteria. For context, the 'rng' argument is a collection of cells that have values satisfying a certain criterion. The code that almost totally works is:

Private Sub set_cell_borders(rng As Range, change_cat As Long)
    Dim border_colour As Long, the_borders As Variant, i As Long
    the_borders = Array(xlEdgeLeft, xlEdgeTop, xlInsideHorizontal, xlInsideVertical)
    
    On Error GoTo errHandler
    Select Case change_cat
    Case 1: border_colour = RGB(160, 160, 160)  ' Trivial change (grey)
    Case 2: border_colour = RGB(255, 192, 0)    ' Minor change (orange)
    Case 3: border_colour = RGB(255, 0, 0)      ' Major change (red)
    Case 4: border_colour = RGB(102, 0, 204)    ' String change (purple)
    End Select
    
    For i = LBound(the_borders) To UBound(the_borders)
        With rng.Borders(the_borders(i))
            .Color = border_colour
            .Weight = xlThick
            .LineStyle = xlContinuous
        End With
    Next i
Exit Sub
    
errHandler:
    ' Assume we got here if rng is format-protected, so simply exit
    Err.Clear
End Sub

The cells all format as expected, except the xlInsideVertical and xlInsideHorizontal borders only seem to acknowledge the .LineStyle = xlContinuous line, not the .Width or .Color attributes i.e. I end up with all the TOP and LEFT borders of each contiguous block of cells formatted as expected, but any adjacent cells that should be marked have simply a thin black line between them. For clarity, all cells start from a wiped-format condition (no borders at all), so I know the .LineStyle attribute is being applied by my code, not simply exists already in those cells, if that makes sense?

I thought I had this working previously, so I suppose it could be an Excel bug in Excel 365 Version 2202, but wanted to check on here whether I am missing something more fundamental.

Update: added images to clarify the intended (top image) and actual (bottom image) behaviour.

CodePudding user response：

I can confirm this behavior. For me, this happens only for the xlInsideVertical-borders (it works for the xlInsideHorizontal), and it happens only when you have a non-contiguous range. Checking with the debugger shows that the properties for Weight and Color are not stored. I cannot say if this is a bug or on purpose.

However, there is an easy workaround: Simply loop over all areas within your range. Every Range has a collection of areas (ranges) which represent the (contiguous) range-pieces of that range. Using areas will work also for contiguous ranges: They have a collection with one member, so your code will work in any case.

Dim area As Range
For Each area In rng.Areas
    For i = LBound(the_borders) To UBound(the_borders)
        With area.Borders(the_borders(i))
            .LineStyle = xlContinuous
            .Weight = xlThick
            .Color = border_colour
        End With
    Next i
Next area

Update: I did some experiments: The problem does not occur if you have a (non-contiguous) range selected and use Selection (or Set rng = Selection). However, as soon as you use Union, the problem occurs. Anyhow, looping over areas did the job in all cases. And in the meantime, I had also cases where xlInsideHorizontal didn't work.

CodePudding user response：

Thanks to @FunThomas for doing some experimenting - it seems the root cause is if the first cell in the 'rng' Range is a lone cell. If the first few cells are contiguous, Excel does not seem bothered by lone cells being part of the collection, it will still apply internal borders for the blocks of contiguous cells as expected.

Therefore, the proper (but slow on large ranges) solution is as @FunThomas suggests - loop through Areas.

In my case, I will think about whether to artificially ensure the first included cell is not isolated from the rest, perhaps by duplicating it in the row below, and then tidying up at the end. This is only worth the effort because my Range is thousands of cells long and the speed difference is noticeable for users, for whom this exercise must be imperceptibly fast.