Make cells containing days of the week, or any other data, stand out with this simple piece of VBA code.
I use an Excel spreadsheet to keep track of my time spent on projects and other things during the day. It features a simple list of dates and days of the week, times and tasks as shown below.
I wanted to colour the cells depending upon what day of the week it was. Using tips from “Microsoft Excel VBA Programming for Dummies 2010” by John Walkenbach I came up with the code below.
Once I got some code together to walk through the cells I wanted to change, the rest was sort of straight forward. The subroutine starts at cell B3 and extends the selection down to and including the last used cell, that is A148 in this sheet.
I could not get the
Application.WorksheetFunction.Text to work in the subroutine. On the sheet you get the short days of the week shown under the “DOW” column. But when the cells have been filled down to get a sequence of dates the Text function only returns what the sheet shows and it does no formatting to the required Mon to Fri text that I need. I found that getting the sheet to do the conversion works ok, hence the extra column “B”.
From there a Select command choses the required colour. The For/Next for Sat and Sun simply extends the shading across a few columns.
Sub ColorCellsByDOW() Dim cellDOW As String Dim cellColor As Long Dim Cell As Range Dim singleCell As Range Dim n As Integer Range(Range("B3"), Range("B3").End(xlDown)).Select For Each Cell In Selection 'Debug.Print Application.WorksheetFunction.Text(Cell.Text, "DDDD") Select Case Application.WorksheetFunction.Text(Cell.Text, "DDDD") Case "Monday", "Mon" cellColor = RGB(219, 238, 243) Cell.Interior.Color = cellColor Cell.Offset(, -1).Interior.Color = cellColor Case "Tuesday", "Tue" cellColor = RGB(218, 218, 66) Cell.Interior.Color = cellColor Cell.Offset(, -1).Interior.Color = cellColor Case "Wednesday", "Wed" cellColor = RGB(214, 150, 200) Cell.Interior.Color = cellColor Cell.Offset(, -1).Interior.Color = cellColor Case "Thursday", "Thu" cellColor = RGB(168, 168, 226) Cell.Interior.Color = cellColor Cell.Offset(, -1).Interior.Color = cellColor Case "Friday", "Fri" cellColor = RGB(246, 173, 100) Cell.Interior.Color = cellColor Cell.Offset(, -1).Interior.Color = cellColor Case "Saturday", "Sat", "Sun", "Sunday" cellColor = RGB(242, 221, 220) For n = -1 To 4 Cell.Offset(, n).Interior.Color = cellColor Next n Case Else Cell.Interior.Color = RGB(255, 255, 255) End Select Next Cell End Sub
The Interior.Color property does not over ride certain Excel settings so the shading will not change. Make sure that all conditional formatting rules are turned off.
It took the best part of the day to get to this solution and that includes reading a couple of books about VBA for Excel programming but it was worth it. I now have the basics for VBA for Excel programming, and I also know of a few of its bugs. Maybe it’s time to upgrade to Office 2010.
Leave a Reply
You must be logged in to post a comment.