Laziness is a true curse!

It's been a month since I wrote my last post... and I'm not proud of this. Let's leave it and realize at least some of the promises I made weeks ago.

Subroutine Update_Row_Colors() works in the following way:

for each row between 1 and 9999 it checks wheather text in column C matches word "red" or not. In the first case, cells in columns A to F of the current row are coloured red. In the second case, the cells get no colouring.

This idea is translated to VBA as follows:

• The variables of appropriate types are declared. Declaration of variable type may be ommited, however for some reasons it is a good practice to provide it. 
  
  Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String

LRow variable keeps number of the current row. LCell keeps name of the cell which may contain word "red". LColorCells keeps string describing range of cells that must be coloured if the match was found. 
• Each row is processed separately in while loop which is executed as long as value of LRow variable (incremented by one in each step of the loop) is less than 10000.
  
  'Start at row 1
LRow = 1
'Update row colors for the first 10000 rows
While LRow < 10000
...
LRow = LRow + 1
Wend
  
  
• For each row values of variables LCell and LColorCells are appropriately set. '&' is the VBA concatenation operator, glueing values together to make a single string.
  
  LCell = "C" & LRow
'Color will changed in columns A to F
LColorCells = "A" & LRow & ":" & "F" & LRow
  
  




• Then text in cell in column C is checked and if its red appropriate actions are taken. ColorIndex property of the cell range interior is set to 3 (index of red colour in the Excel colour palette) and background pattern is set to solid if the mach is found. Otherwise cell range gets no styling.
  
  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
  
  

In my next post I would like to show how to use Excel colour palette to pick the colour we want to use and how to interactively provide text we want to look for in cells.