Search code examples
excelvbaformattingborderhidden

Border on hidden ranges show when VBA is used to modify cell formats (including borders) across a sheet

I develop models for clients in Excel, and often times need to adjust the color schemes of a single model to match those of a client's logo/brand. I decided to construct a tool in VBA to adjust the color scheme across a workbook according to a set color pallet. When I run the VBA tool, it adjusts existing color formats to the new color scheme. Some ranges are hidden in the workbook. I adjust the format for hidden ranges as well, as settings could be changed unhide the cells. The VBA tool loops through each cell in a sheet (up to the last used row and column) and adjusts formatting colors.

The problem arises with the borders. If we take an example, assume cell $B$2 is hidden and has a border. As the tool loops through the cells it will arrive at $B$1 and detect a bottom border, intended for $B$2, and change the color. $B$1 is not hidden in the sheet, and I think that the border format change causes Excel to "think" that $B$1 has been assigned a border. Thus when the macro is finished, the bottom border on $B$1 will show as a line (when I need it to remain hidden).

If I understand things correctly, somehow Excel "knows" if a border is intended for a specific cell because it hides the border when the cell is hidden. Does anyone know what property this is? If I knew, I could check the property, and only apply the border change when it is of a correct value. Other ideas?

I provide an abridged version of the code below. Many thanks for any support you can provide!

Sub SwitchSheetColorScheme(ByVal strShtName as String)
Application.ScreenUpdating = False
Set rngSheetRange = GetActiveShtRange(strShtName) 'reduces sheet range to in-use range
Set rngNewColors = Range("cntrl_new_colorCode_rng") 'range of new color numeric values
Set rngOldColors = Range("cntrl_old_colorCode_rng") 'range of old color numeric values
  
For i = 1 To rngNewColors.Count
    For Each cell In rngSheetRange
        Dim ColorObjects As New Collection
            ColorObjects.Add cell.Borders(xlEdgeLeft)
            ColorObjects.Add cell.Borders(xlEdgeRight)
            ColorObjects.Add cell.Borders.Item(xlEdgeTop)
            ColorObjects.Add cell.Borders.Item(xlEdgeBottom)
                
        Dim attrib As String
        attrib = "Color"
            
          
            For x = 1 To ColorObjects.Count
                If CallByName(ColorObjects(x), attrib, VbGet) = rngOldColors(i, 1) Then
                    ColorObjects(x).Color = rngNewColors(i, 1)
                End If
            Next
     
        
        Set ColorObjects = Nothing

    Next
Next

Application.ScreenUpdating = True

End Sub

enter image description here

Solution

  • Okay, I think I finally got it figured out. At least I have a solution that seems to work. Where I went wrong was that I was focusing on the borders themselves in the hopes of finding a property that indicates to which cell the border belongs: Range("SomeRangeWithBorders").Borders.Item(xlEdgeTop).[SomeSpecialProperty]. But what I have found is that the Range property Borders.LineStyle(i) where i is an integer between 1 and 6 (four outside borders, and two diagonal borders), will return the value -4142 when there is not a border assigned to the cell. This -4142 value persists even in cases where adjacent cells have a shared border with the cell and the border was was assigned to the adjacent cell. So I first check if the .Borders.LineStyle(i) property is equal to -4142, and if its not, then I change the border format.

    So, in summary, I the line reading:

    ColorObjects.Add cell.Borders(xlEdgeLeft)

    Would be replaced with:

    If cell.Borders(1).LineStyle <> -4142 Then
     ColorObjects.Add cell.Borders(xlEdgeLeft)
End If

    I hope this is helpful to that one special person who runs into a similar problem!