Colouring Excel Cells by Day of Week

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.

Setting Excel cell colours

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

Shading Problems

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.

Conclusion

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.