Laziness is a true virtue!

Being lazy is a virtue. Especially if what you have on your mind is not the coach potato kind of laziness but something more like "I will spend this hour working to save fifteen minutes of some dull everyday work" laziness. Generally, this blog will be about me being lazy and happy so better get used to it.

Example. Your boss gives you an Excel spreadsheet, 10 thousand rows of data, and tells you that all those rows which have text 'red' in column C should have green background. You can check each row and spend whole day updating the document. You can reduce time spent on this fascinating task to two hours being clever and using the 'Search' option.

But you can do it in a single mouseclick! However, first you have to write a macro, a small piece of code that will do the work for you. Macros for Excel are written in Visual Basic for Applications (VBA) language. And here is the solution to our problem, row-colouring function.


Sub Update_Row_Colors()

    Dim LRow As Integer
    Dim LCell As String
    Dim LColorCells As String
    'Start at row 1
    LRow = 1

    'Update row colors for the first 10000 rows
    While LRow < 10000 
       LCell = "C" & LRow 
       'Color will changed in columns A to F 
       LColorCells = "A" & LRow & ":" & "F" & LRow
       Select Case Range(LCell).Value
           'Set row color to light yellow 
           Case "red" 
               Range(LColorCells).Interior.ColorIndex = 3
               Range(LColorCells).Interior.Pattern = xlSolid
           'Default all other rows to no color
           Case Else 
               Rows(LRow & ":" & LRow).Select
               Range(LColorCells).Interior.ColorIndex = xlNone 
       End Select 

       LRow = LRow + 1 
    Wend Range("A1").Select

End Sub 

I had a very similar problem to solve recently. For a couple of days I was doing such colourings manually. Finally I was fed up with this enough to find different solution at this page. The above code is based on the one I found at Tech on the Net.

In the future I would like to extend this example to make it more flexible. If you want to know what interesting can be done about it, follow me on this blog. Believe me - I am about to prepare even more lazy version of it.

Next time I will start from going line by line through the code to describe in detail what is really happening there and then I will move on to showing some lazy extensions. Stay tuned!