• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Custom Formatting / Custom Template

MSC Bobs

Member
Hi everyone,

I'm developing a custom template and I've run into a formatting issue I'm hoping someone here has dealt with and could offer some help.

I'm creating a sheet that I want to be dark, e.g. "night time." It's a pretty dark shade of grey. I'm also the kind of user who likes to toggle the gridlines on and off.

So, when I set the color of my sheet to this darker grey, I noticed the gridlines no longer toggle. Am I doing something wrong with setting the default color of my cells, or is VBA the only way to fix the gridline toggle once I change the default color of the cells from white to something else?

If VBA is the only solution, could someone help me out with that code?

Thanks to anyone who can help.
 

Attachments

MSC Bobs

Member
Isn't it simply because you have defined cell borders? Set them to no border, I guess it will work.
I tried that. The gridlines wouldn't even display when I changed the cell color to dark. That's when I started to wonder if it's a cell issue and not a border issue.
 

Peter Bartholomew

Well-Known Member
The default gridlines only show if there is no fill set for the sheet. Even a white fill will cover the grid so that it can no longer be toggled on and off (I use that as part of my Normal style because I do not use the grid for referencing data and only wish to see cell borders in defined ranges).
If you wish to see a grid you could use cell borders and toggle between styles or use VBA to insert borders.
 

Belleke

Active Member
Hi,
Try
No borders
Code:
Sub NO()
Sheets("dark").Cells.borders.LineStyle = xlNone
End Sub
Borders
Code:
Sub YES()
With Sheets("dark").Cells.borders
    .ThemeColor = 1
    .TintAndShade = -0.499984740745262
End With
End Sub
 

MSC Bobs

Member
Hi,
Try
No borders
Code:
Sub NO()
Sheets("dark").Cells.borders.LineStyle = xlNone
End Sub
Borders
Code:
Sub YES()
With Sheets("dark").Cells.borders
    .ThemeColor = 1
    .TintAndShade = -0.499984740745262
End With
End Sub
Thank you, I appreciate it.
 

GraH - Guido

Well-Known Member
Either I did not understand the query, either I'm misunderstood.
I don't see a need to define any cell border colour, just fill it with that dark grey.
-> default grid will be visible
-> in view, uncheck the Show Gridlines option -> you won't see the cell "borders"
At least with the uploaded file I got it to work like this by defining the cell borders as none and I could toggle the gridlines on/off via this view setting.
 

Peter Bartholomew

Well-Known Member
Guido
Just try it. Apply a fill (dark, light or white) to any range you choose. The default grid no longer shows within or on the boundary of the range and View Gridlines only affects the lines outside the newly-colored range. At least, that is the way it is on a pc. Maybe, if you are a Mac user life may be different.
 

GraH - Guido

Well-Known Member
Hi Peter,
Thx for the additional feedback.
I'm on Windows too ... Could have sworn it was working like that a couple of days ago. Now I'm failing to replicate - wth. Did install updates yesterday, though I don't see the link.
And more puzzling... in the uploaded workbook, I can't set the gridlines back at all, even after having removed the dark interior colour on the sheet named "dark". For the other sheets it is working. Going to need an extra coffee I assume before going off to work.
 
Top