Excel VBA to change border color - only visible borders

3
votes

I have an Excel form whose borders are in black color. I would like to change it to other color. I tried the following code:

ActiveSheet.UsedRange.Borders.Color = RGB(255, 0, 0)

It changed the borders of all cells, including those cells which did not have borders, into red. This is not what I want. I want those borders in black to turn red and the invisible borders to stay invisible. Is there a way to do it?

3
excelvba
You can loop through the UsedRange like in the answer below. - Mikku
@JvdV As a test, I tried ActiveSheet.Range("P27:X30").SpecialCells(xlCellTypeSameFormatConditions).Select. It did not select anything. Any sample code would be welcome. - joehua
@joehua, excuses, thought you meant conditional formatting. Either way, I also added an interesting approuch to this matter that don't require looping all cells. - JvdV

3 Answers

2
votes

Just another way of doing things making use of FindFormat and ReplaceFormat properties. 

Sub BordersReplace()

With ThisWorkbook.Sheets(1)
    For X = xlEdgeLeft To xlEdgeRight
        With Application.FindFormat.Borders(X)
            .Color = 0
        End With
        With Application.ReplaceFormat.Borders(X)
            .Color = 255
        End With
        .Cells.Replace What:="", Replacement:="", searchformat:=True, ReplaceFormat:=True
        Application.FindFormat.Clear
        Application.ReplaceFormat.Clear
    Next X
End With

End Sub

Small loop involved to go through the appropriate XLBordersIndex enumeration.

Note, not clearing FindFormat and ReplaceFormat will make Excel keep working with the first used format, hence why the .Clear is nesseccary.

I myself am a little bit puzzled on why it would't work on the cells with all edges on its borders applied. For that to work use Application.FindFormat.Borders()

1
votes

Thanks for Mikku's input, I got the following code to work.

Sub change_border_color()

'change the color of existing borders
Dim cell As Range

Application.ScreenUpdating = False

For Each cell In ActiveSheet.UsedRange

If cell.Borders(xlEdgeLeft).LineStyle = 1 Then
    cell.Borders(xlEdgeLeft).Color = RGB(0, 0, 255)
End If

If cell.Borders(xlEdgeTop).LineStyle = 1 Then
    cell.Borders(xlEdgeTop).Color = RGB(0, 0, 255)
End If

If cell.Borders(xlEdgeBottom).LineStyle = 1 Then
    cell.Borders(xlEdgeBottom).Color = RGB(0, 0, 255)
End If

If cell.Borders(xlEdgeRight).LineStyle = 1 Then
    cell.Borders(xlEdgeRight).Color = RGB(0, 0, 255)
End If

Next

Application.ScreenUpdating = True

End Sub
0
votes

Use this:

A Loop will work fine. Currently you are setting the complete Range and changing it's border, you only need to do that with cells having any Value.

This loop will colour the Border Red if cell currently have any border.

For Each cel In ActiveSheet.UsedRange

    If Not cel.Borders(xlEdgeLeft).LineStyle = 0 Then

        cel.Borders.Color = RGB(255, 0, 0)

    End If

Next

This loop will color the Borders where the cel have some Value.

For Each cel In ActiveSheet.UsedRange

    If Not cel.Value = "" Then
        cel.Borders.Color = RGB(255, 0, 0)
    End If

Next