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.
Quick notes –
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