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!
