This one was prompted by a problem posed on an Excel forum, for which I provided a solution. The original issue was actually to do with hiding/unhiding rows programmatically based on user selection from a drop-down, a function that required use of the Worksheet_Change event and Set Intersect, with additional code to hide/unhide…but that’s beside the point, so we’ll skip that one for now.The thing that grabbed my attention was the additional question: Is there a way for the above to hide/unhide 2 rows at a time?
The answer, of course is yes, but I was more intrigued by why this was wanted, and the answer to that was : formatting purposes…it “looks” prettier with a row in between each cells.
The problem: making Excel pretty –
To give an idea of this “prettier” concept here are a couple of shots, showing the double row formatting:
And, actually, it’s hard to disagree with the point that with the first it is slightly easier to see the entry points/input cells than by using single-spaced rows. But that said, it still doesn’t really do it for me. So, my preferred method? Set up custom styles and use those.
The advantages of using custom styles are (at least) two-fold; firstly they are simple to use in VBA code, for instance with new rows/cells and a style you’ve (cunningly) named “myNewStyle”, you could simply append the code:
.Style = "myNewStyle"
to the relevant code, as opposed to applying the same code in the recorded VBA way:
With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlTop .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 1 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 16 End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 16 End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With
Um, yeah, the VBA recorder kept it simple, eh?
The other advantage may be less obvious at first glance, but is to do with speed. If you have amended the style of a cell by simply changing several parameters and then want to copy it across thousands of rows/cells, Excel will take longer to do this (as it effectively checks and copies each parameter in each cell, as above), than if it simply applies a pre-defined style, for which it already “knows” the parameters.
The nitty-gritty: how to add custom styles –
For the style I’m going to demonstrate here the best background is light grey, so firstly set the Fill-Colour for all cells to Grey-25%. Trust me, grey is under-rated…I’ll put something up about colour use another time.
Now select a single cell so that you can create your first style.
Choose Format>Style from the Excel menu-bar:
Enter your new style name, in this instance “myNewStyle”, and select “Modify:
Choose the font, alignment and other settings as you would normally, in this case I’ve chosen to Right (Indent) at 1 point:
Now select the “Border” tab. In here I’m going to do a mixed border; left and top will be done in Grey-50%, bottom and right will be done in White (this part is important, if just left blank then we won’t get the required effect on our now Grey-25% background):
Now choose the “Patterns” tab, and select Grey-25%, since this will match our previously selected background colour, and press “OK” to take us back to the main Style tool. Press “Add”, which will, of course, add our new style to the style palette, and then “OK” to close the tool.
So, our new style is ready to use, and by adding it to the required cells (and any other styles I’ve added e.g. for the headers, totals etc), we end up with a worksheet with clearly delineated areas for data entry, headers etc:
So, no double spacing, clear input areas, faster code running, easy application to new cells and, if we remove grid-lines, row and column headers and tabs from the view options it even begins to look like a real application! There are, of course, other ways to format this as well, playing upon the fact that visual markers are important for people, but for starters, well…what say you?
A Final Note:
Did this make Excel pretty? No, nothing we do will make Excel pretty, but perhaps we can call it acceptable and make sure it’s at least welcome at the ball…
Applies to: Excel 2003 and earlier