Automatically formatting separator lines. 1

[ColourfulFigsnDiags]

Just thought I'd pass on this little bit of code I wrote this morning (it is nothing fancy)- it may or may not be helpful for others, but it certainly was for me!

I have a spreadsheet which lists the contents of boxes. I wanted to put a separating line (thick black line) on the row where one boxes contents ended, and the next ones began.

Problem was I had 35,000 lines of contents- to do this manually probably would have taken a while

Anyway, here is some basic code to do it in 10 seconds...

Sub format-row()
' Format row with thick black line whenever the box number changes ...

' loop over all rows
    For i = 1 To 35001
' get the previous box number
        pbox = Range("B" & i).Value
'get the current box number
        cbox = Range("B" & i + 1).Value
'if the two are not the same, then we have gone to a new box

        If (pbox <> cbox) Then
'therefore format row with a thick black line
            Rows(i & ":" & i).Select
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
        End If
    Next i
End Sub

Read the Eng-Tips Site Policies at FAQ731-376

 

[krossview]

Hi ColourfulFigsnDiags

You can actually do this with zero programming by using the Excel's "Condition formatting".

Condition1 = Dropdown box = Abs/Rel Address
[blue]Cell Value Is[/blue] = Not Equal to = [green]$A1[/green]

Now select desired format option to change cell Font, Border or Pattern.

However this is not the method that you want as you want to add a separation for the whole row or row in your range of data.

A second method accomplishes this, which allows you to lock the changes to the column of interest.

Condition1: Create formula here
[blue]Formula Is[/blue]: =$B1<>$B2

The conditional format is set when the formula is true. No formating is applied when the formula is false. You can apply multiple format changes at the same time like bold font, separating line, back ground color etc. Apply format to whole spreadsheet or only your range of data.

Playing around with Conditional Format opens up a whole new world of neat display options. Examples I use are
1. Bold font for 1st item that's different.

2. Making simular items after 1st item invisible by selecting same font color as background color. This creates the same effect as a pivot table.

3. Creating a color pattern in a table to show two or three different conditions. Each color pattern will can span several rows and columns depending on the condition va
Example:
Condition1: Cell Value Is [highlight green][white]"Less than" 50[/white][/highlight]
Condition2: Cell Value Is [highlight fuchsia][white]"Between" 50 "and" 100[/white][/highlight]
Condition3: Cell Value Is [highlight red][white]"Greater than" 100[/white][/highlight]

Be sure to toggle the F4 key to set the correct absolute/relative addressing as Excel uses an Absolute address by default. Most my problems are caused by this.

The beauty of using conditional formation is that you do not have to reformat your work if you make changes. Experiment and have fun.

[blue]Krossview[/blue]
Results with different view...

 

[ColourfulFigsnDiags]

Very true, didn't think of that- and it has the added bonus of not requiring a re run every time you insert data! Star for you my friend!

Read the Eng-Tips Site Policies at FAQ731-376

 

[electricpete]

A related question which comes to mind.

When copying conditional formatting we have to use the copy-format-paintbrush.

The range that you copy to has to be selected with a mouse.

I have trouble selecting large blocks of data with a mouse. You have to drag beyond the data on the screen and wait for scroll... if you happen to let go you've got to start all over. There is no way to use the end/arrow combination to jump to the end of the data when using the mouse vs keyboard to select data. And mouse is required for this funciton, correct?

Bottom line question - is there any way to use the format painter without having to use mouse to select the copy to range?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[krossview]

electricpete

Sorry for the late response, but any response is better than no response. To answer your question: Yes there is and there are several ways to achieve this.

1) See Excel Help "Move and scroll in End mode". Help "Navigation Keys", shows Excel uses the CTRL key instead of the END key for navigation.

2) To select a range hold the SHIFT key prior to using END/ARROW key. Excel will select all data providing cells are not blank.

3) If you have a problem with this and Excel behaves different, check out the Tools-Options-Transition tab, where you can check navigational settings and make Excel behave like Lotus 123. However, I never experienced any problems and left the default settings as is.

4) Add a toolbar button called "Select Current Region", which selects the whole region around the active cell.
This is much faster than END+ARROW RIGHT and END+ARROW DOWN. The Sort function uses this function to select all data in one continous block prior to sorting.


I find the format painter very cumbersome, just like you and hardly use it. Instead I customized my toolbar and added the following toolbar buttons "Paste Formatting" and "Paste Values". Instead of using the format painter I copy the cell(s) using shortcut CTRL+C and click "Paste Formatting" toolbar to quickly copy the format to the new location. There are multiple benefits to this method.

a) I can past the value in one cell and past the format to multiple cells after that.
b) You can quickly repeat the by clicking on the "Past Formatting" toolbar again or by pressing the "F4" function key to repeat the action (Also CTRL + Y)

c) The real power is in copying a range of cells with different formatting. You have to only select a single cell to paste the formatting of the copied cell range.
d) Use "F4" key again to repeat this by either selecting a single cell and pressing F4 key after each cell selection or by holding the CTRL key and selecting all single cells and the pressing the F4 key to past the format. Both methods work very fast.

Excel used to have a short-cut to "Paste formatting", which was SHIFT + CTRL + V. However, this shortcut never worked and is not listed anymore in Excel 2003.

TO answer you last question: You can not totally get away from using the mouse, as you need to click on the "Paste Format" toolbar, but the selection and moving around you can without a mouse.

To add the toolbar button, right-click on any toolbar. Select Customize-Command tab. Select category "Edit", scroll down and drag "Paste Special", "Paste Formatting and "Select Current Region (bottom) to formatting toolbar. I have added all the toolbars I use a lot, which really increase my work speed and efficiency. To have all toolbars visible I select the checkbox "Have Standard and Formatting Toolbars on two rows" under the Customize-Options tab.

Good luck and happy experimenting with these hidden treasure as you will find a quite a few of them.

BTW: Excel stores the toolbar configuration in the XLB file in folder "C:\Documents and Settings\username\Application Data\Microsoft\Excel"

[blue]Krossview[/blue]
Working to produce results...

 

[joerd]

If you really don't like the mouse:
Copy using Ctrl-C
Paste formatting using Alt-E S T
(= selecting menu options Edit, Paste Special, Formats)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.