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
