Thanks to some friendly help I was able to find what I needed.
Below, after ---Macro Start---, is a macro that will set the background color of a row based on the value in a given cell.
The macro is as follows and some comments in to make it a bit easier to read. (note comment lines starting with ' )
--- Macro Start ---
Sub Update_Row_Colors()
Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
' Start at row 1
LRow = 1
' This will look at the first 2000 rows, if you have more than 2000, change this value
While LRow < 2000
' This sets the column for which you are testing, in this example, column C
LCell = "C" & LRow
' Row background color will be changed in columns A to K, if your columns extend past K, change the below "K" to the appropriate column
LColorCells = "A" & LRow & ":" & "K" & LRow
' Indicates how many characters in the column you want to evaluate
Select Case Left(Range(LCell).Value, 6)
' Set row color to light blue if column value = 007007
Case "007007"
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid
' Set row color to light blue if column value = 030087
Case "030087"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid
' Set row color to light blue if column value = 063599
Case "063599"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
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
-- Macro End --
All you should need to do is add the code (blue text) to the spreadsheet, by going to Tools -> Macro -> Visual basic Editor, paste the above code, change the values to suit your requirements and then click the run button. That should do the trick.