Excel VBA: Set the background-color & border for a user-defined range of rows

Question 1

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.

Question 2

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.

Leave a Reply Cancel reply