3
\$\begingroup\$

On my work I've got to maintain an Excel-sheet. Documenting the incident-tickets I've dealt with.

For to be able to distinguish between single days easily I mark every second day with a light cyan-background. So the sheet gets a Zebra-stripe look.

Because I've got to do this every second day (by marking the concerning row-range with the mouse) I've made myself a VBA procedure.

It is started from the Excel-GUI.

An input box appears in which I enter the start- and the end-row. Separated by a colon. The VBA then marks the range between start inclusive and end inclusive. Moreover it adds a bottom-border to the last row.

Here the VBA-code:

' Sets the background of a user-given range to
'  a defined color.
' Sets the bottom-border of the last row within
'  the range.

Public Sub SetBackgroundColorAndBorder()
    Dim StartEnd As Variant ' Becomes an array with the startRow as first element and the lastRow as second element.
    Dim VerticalIndex As Integer
    Dim HorizontalIndex As Integer

    Const COLOR_INDEX As Integer = 34

    On Error GoTo ErrorHandler

    ' Process the user-given instruction.
    StartEnd = Split( _
                    Trim( _
                        InputBox("Please use a colon for separation." _
                            & vbCrLf & "E.g.: 21:28", _
                            "Please insert start- and endrow!")), _
                     ":")

    ' Set the background-color.
    For VerticalIndex = StartEnd(0) To StartEnd(1)
        For HorizontalIndex = 2 To 6
            Cells(VerticalIndex, HorizontalIndex).Interior.ColorIndex = COLOR_INDEX
        Next
    Next

    ' Set the bottom-border of the last row within the given range.
    For HorizontalIndex = 1 To 6
        Cells(StartEnd(1), HorizontalIndex).Borders(xlEdgeBottom).LineStyle = xlDouble
    Next

    Exit Sub

ErrorHandler:
    MsgBox "Please restart the macro." & vbCrLf _
        & "Please take care to give a correct instruction.", _
        vbExclamation, "AN ERROR HAS OCCURED."
End Sub

It works fine. But I would like to know:

What do you think?

Could it be improved concerning stability and efficiency?

What do think about asking the range from the user via input box?

Could one accomplish the same task in an easier perhaps more robust way?

General hints and comments concerning my coding welcomed too.

\$\endgroup\$
  • \$\begingroup\$ How are you identifying the days? Specifically, is there a date field you are looking at to say "this is from the 1st of March and this is from the 2nd"? If so there is an opportunity here to reduce the user interface to a button click, by having your code look for the date changes so you don't have to specify the range manually. \$\endgroup\$ – Winterknell Mar 13 '17 at 7:09
  • \$\begingroup\$ You can also have this functionality run automatically when the workbook is opened, or the worksheet is activated, or if the worksheet is changed. A specific action by the user to re-color the sheet wouldn't be needed then. \$\endgroup\$ – PeterT Mar 13 '17 at 14:31
  • \$\begingroup\$ The first column contains indeed the date. That's an intersting suggestions. Every even day colored (or vice-versa). Thanks a bunch. :) \$\endgroup\$ – michael.zech Mar 13 '17 at 14:51
  • 1
    \$\begingroup\$ Sorry for being a pain, but why not convert your range to a table (Format as table). Select the right format and Excel will handle the striping for you. No VBA required. \$\endgroup\$ – jkpieterse Mar 13 '17 at 17:01
  • 1
    \$\begingroup\$ @PeterT A common Excel problem seems to be that it offers a lot of solutions most people don't know. ;) Anyway: Thanks a lot. \$\endgroup\$ – michael.zech Mar 14 '17 at 3:46
1
\$\begingroup\$

Quick notes -

The inputbox can restrict the input by the type argument.

You can also just format it as a table and then unlist the table to keep it as a range -

Sub example()
    Dim targetTable As ListObject
    Dim targetRange As Range
    Set targetRange = Application.InputBox("Select Range", Type:=8)
    Set targetTable = ActiveSheet.ListObjects.Add(xlSrcRange, targetRange)
    targetTable.TableStyle = "TableStyleMedium16"
    targetTable.Unlist
End Sub

Now just make your own custom table style and you're good to go.

Also, integers are obsolete. According to msdn VBA silently converts all integers to long.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.