VBA - Color borders of a cell range white

Question

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

Could you expand on "does not work"? What happens? Nothing? You get an error message? etc... — cybernetic.nomad, Jan 24 '20 at 21:38
@cybernetic.nomad - No error message but the borders do not get white colored. — Jrules80, Jan 24 '20 at 21:40
@BigBen - How do I hide the grid programmatically? I need grid for other worksheets in the book — Jrules80, Jan 24 '20 at 21:41
https://stackoverflow.com/questions/40368373/how-can-i-turn-off-gridlines-in-excel-using-vba-without-using-activewindow — BigBen, Jan 24 '20 at 21:44
Can I use specific worksheet name instead of Active Window. I generally have an attention span of 3yo and I keep flipping between worksheets while the data is being loaded. And last I clocked, it took 6 whole seconds to load the worksheet in question. Thanks. — Jrules80, Jan 24 '20 at 21:46
No because `DisplayGridlines` is the property of the `Window`, but see the link. — BigBen, Jan 24 '20 at 21:48
@BigBen - I sorta did. In a crunch for time right now but using ActiveWindow clearly means that focus must be on the window that I want to remove borders for? (DUH!?) — Jrules80, Jan 24 '20 at 21:53
I don't necessarily think so based on a read of that question/answer, specifically the edit, but I haven't tested it yet. — BigBen, Jan 24 '20 at 21:56
You need to pass a worksheet *object* as the parameter, not a string. — BigBen, Jan 24 '20 at 22:02
I am lost, bud :( And I am sorry that my company's firewall is blocking the chat window. Otherwise, I would have preferred a quick chat. — Jrules80, Jan 24 '20 at 22:06
To pass the worksheet: `TurnOffGridLines Worksheets("Deliverable-Epic-Story Progress")` — cybernetic.nomad, Jan 24 '20 at 22:14
@BigBen yes, realized that 2 seconds after hitting Enter... :) — cybernetic.nomad, Jan 24 '20 at 22:15

BigBen · Accepted Answer · 2020-01-24T22:20:43.073

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.

jclasley · Answer 2 · 2020-01-24T21:52:35.297

0

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)

edited Jan 24 '20 at 21:52

answered Jan 24 '20 at 21:40

jclasley

668
5
14

OP wants white borders. – BigBen Jan 24 '20 at 21:41
Edited to reflect comment. I work almost exclusively in Word, and it's `wdWhite` almost always – jclasley Jan 24 '20 at 21:42
@BigBen again, Word vestige. Also, he says he wants it to appear borderless. I suggested making it actually borderless. I can edit to give both options. – jclasley Jan 24 '20 at 21:46
That *removes* the border -but the grid is still there. I understand "borderless" to mean no grid. – BigBen Jan 24 '20 at 21:47
My second snippet colors the edge of the cell white, which is what I understand to be the question – jclasley Jan 24 '20 at 21:48
`ColorIndex` depends on the current color palette. I wouldn't use that either tbh. – BigBen Jan 24 '20 at 21:50
@bigben AGH. Happy now? – jclasley Jan 24 '20 at 21:53
Well to be honest, `vbWhite` is equivalent to `RGB(255, 255, 255)` so we're kind of back to where we started. – BigBen Jan 24 '20 at 21:53
@jclasley - I tried your suggestion but I am getting an error. See screengrab above. – Jrules80 Jan 24 '20 at 22:07

VBA - Color borders of a cell range white

2 Answers2