CODEWITHSUNDEEP
excelvba
joehua
joehua

Reputation: 735

Excel VBA to change border color - only visible borders

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?

Upvotes: 3

Views: 6159

Answers (4)

Gary Csepai
Gary Csepai

Reputation: 11

Sub chgBorderColor_On_AllSheets()

'change the color of existing borders on all sheets

Dim Current As Worksheet
Dim cell As Range
Dim Red As Integer, Green As Integer, Blue As Integer
Dim NewColor As Long
Dim i As Integer


Red = Application.InputBox("Input R component of RGB", "Line color definition", Type:=1)

Green = Application.InputBox("Input G component of RGB", "Line color definition", Type:=1)

Blue = Application.InputBox("Input B component of RGB", "Line color definition", Type:=1)
NewColor = RGB(Red, Green, Blue)

Application.ScreenUpdating = False

For Each Current In Worksheets

For Each cell In Current.UsedRange
    
    For i = xlEdgeLeft To xlEdgeRight '7 to 10
    
        If cell.Borders(i).LineStyle = xlContinuous Or _
            cell.Borders(i).LineStyle = xlDouble Or _
            cell.Borders(i).LineStyle = xlDot Or _
            cell.Borders(i).LineStyle = xlDash Or _
            cell.Borders(i).LineStyle = xlDashDot Or _
            cell.Borders(i).LineStyle = xlDashDotDot Or _
            cell.Borders(i).LineStyle = xlSlantDashDot Then
        
            cell.Borders(i).Color = NewColor
            
        End If
    Next
Next
Next

Application.ScreenUpdating = True

End Sub

Upvotes: 1

JvdV
JvdV

Reputation: 75840

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()

Upvotes: 2

joehua
joehua

Reputation: 735

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

Upvotes: 2

Mikku
Mikku

Reputation: 6654

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

Upvotes: 0

Related Questions