CODEWITHSUNDEEP
excelvba
Jrules80
Jrules80

Reputation: 178

VBA - Color borders of a cell range white

I am trying to make a cell look borderless by coloring the edges white. Here is my code and the goddarn thing does not work. Thanks for correcting it.

 Dim cel As Range

For Each cel In Range(Cells(4, 1), Cells(Worksheets("Deliverable-Epic-Story Progress").UsedRange.Rows.Count, 15))
    With cel.Borders
        If .Item(xlEdgeTop).LineStyle <> xlLineStyleNone Then
            .Item(xlEdgeTop).Color = vbWhite
        End If

        If .Item(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
            .Item(xlEdgeBottom).Color = vbWhite
        End If
    End With
Next

** UPDATE ** Pictures attached if it helps.

I used this code from the link @Big Ben shared.

Private Sub TurnOffGridLines(target As Worksheet)
Dim view As WorksheetView
For Each view In target.Parent.Windows(1).SheetViews
    If view.Sheet.Name = target.Name Then
        view.DisplayGridlines = False
        Exit Sub
    End If
Next
End Sub

And I am calling that sub like this and it errors out. My worksheet name is "Deliverable-Epic-Story Progress"

TurnOffGridLines ("Deliverable-Epic-Story Progress")

enter image description here enter image description here

Upvotes: 0

Views: 862

Answers (2)

BigBen
BigBen

Reputation: 49998

First, it seems like you are re-inventing functionality. I'd just hide the grid.

Based on this question, I would add the following:

Private Sub TurnOffGridLines(target As Worksheet)
    Dim view As WorksheetView
    For Each view In target.Parent.Windows(1).SheetViews
        If view.Sheet.Name = target.Name Then
            view.DisplayGridlines = False
            Exit Sub
        End If
    Next
End Sub

And pass it a Worksheet variable, not a String.

Dim ws as Worksheet
Set ws = ThisWorkbook.Worksheets("Deliverable-Epic-Story Progress")

TurnOffGridLines target:=ws

And it you just want to do this manually, View > Gridlines or Alt+W+V+G.

Upvotes: 1

jclasley
jclasley

Reputation: 678

You don't have to turn them white, set the .LineStyle property of Borders to none.

.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone

If you actually want the bottom white:

.Borders(xlEdgeBottom).ColorIndex = 2

or

.Borders(xlEdgeBottom).Color = RGB(255, 255, 255)

Upvotes: 0

Related Questions