Cannot find range in Sub Colour_coding script

I have attached a report which contains a colour coding script was written by an ex colleague. The problem is when running the macro it stops at line 89. I have had to extend this list beyond line 89. I cannot find the "range" within the VBA which identifies what the range selected should be. spent hours trying to work it out and am now stumped.


Before anyone suggests I use conditional formatting, this has to work on Excel 2003 which sadly is limited to 3 conditions.

Hi, MakkyD,

the last loop starts with

z = 6
Do While z < 90

You should try using a more specific way in getting the last row and then use a For lngRow = 44 to LngLR

lngLR = Range("A") & Rows.Count).End(xlUp).Row

Sub Colour_coding()
Dim x As Long, y As Long, z As Long

x = 6
Do While x < 39
  If Cells(x, 6) <> 0 Then
    With Cells(x, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With
  ElseIf Cells(x, 12) = "a" Then
    With Cells(x, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .ColorIndex = 16
    End With
  ElseIf Cells(x, 12) = "x" Then
    With Cells(x, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 15773696
    End With
  ElseIf Cells(x, 12) = 1 Then
    With Cells(x, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 3407718
    End With
  ElseIf Cells(x, 12) = 2 Then
    With Cells(x, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 10092543
    End With
  ElseIf Cells(x, 12) = 3 Then
    With Cells(x, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .ColorIndex = 46
    End With
  ElseIf Cells(x, 12) = 4 Then
    With Cells(x, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 49407
    End With
  ElseIf Cells(x, 12) = 5 Then
    With Cells(x, 10).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 13395711
    End With
  End If
  x = x + 1
Loop

y = 44
Do While y < 86

  If Cells(y, 12) = "a" Then
    With Cells(y, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With
  ElseIf Cells(y, 12) = 1 Then
    With Cells(y, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 3407718
    End With
  ElseIf Cells(y, 12) = 2 Then
    Cells(y, 10).Select
    With Cells(y, 10).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 10092543
    End With
  ElseIf Cells(y, 12) = 3 Then
    With Cells(y, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With
  ElseIf Cells(y, 12) = 4 Then
    With Cells(y, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 49407
    End With
  ElseIf Cells(y, 12) = 5 Then
    Cells(y, 10).Select
    With Cells(y, 10).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 13395711
    End With
  End If
  y = y + 1
Loop

z = 6
Do While z < 90
  If Cells(z, 8) <> 0 Then
    Cells(z, 13).Select
    With Cells(z, 13).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
    End With
  ElseIf Cells(z, 15) = "a" Then
    With Cells(z, 13).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .ColorIndex = 16
    End With
  ElseIf Cells(z, 15) = 1 Then
    With Cells(z, 13).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 3407718
    End With
  ElseIf Cells(z, 15) = 2 Then
    With Cells(z, 13).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 10092543
    End With
  ElseIf Cells(z, 15) = 3 Then
    With Cells(z, 13).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .ColorIndex = 46
    End With
  ElseIf Cells(z, 15) = 4 Then
    With Cells(z, 13).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 49407
    End With
  ElseIf Cells(z, 15) = 5 Then
    Cells(z, 13).Select
    With Cells(z, 13).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 13395711
    End With
  ElseIf Cells(z, 15) = "x" Then
    Cells(z, 13).Select
    With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 15773696
    End With
  End If
  z = z + 1
Loop
End Sub

Ciao,
Holger