Xtykutl

I am still a novice VBA user. I can't seem to get my head around why this bit of script is not working. I get a

'Subscript Out of Range' error

on the second line in the If statement of the below:

Sub ScreenUpdate()



Dim LastRow As Long, LastColumn As Long, i As Long, j As Long



Application.ScreenUpdating = False



    LastRow = Range("C" & Rows.Count).End(xlUp).Row

        For i = 2 To LastRow

            If Cells(i, 17).Value = Cells(i - 1, 17).Value Then

                Cells(i, 1).NumberFormat = ";;;"

                Cells(i, 2).NumberFormat = ";;;"

                Cells(i, 15).NumberFormat = ";;;"

                Cells(i, 16).NumberFormat = ";;;"

            Else

                Cells(i, 1).NumberFormat = "0"

                Cells(i, 2).NumberFormat = "0"

                Cells(i, 15).NumberFormat = "0"

                Cells(i, 16).NumberFormat = "0"

            End If

        Next i



        For j = 2 To LastRow

            If Cells(j, 17).Value Mod 2 = 1 Then

                Range(Cells(j, 1), Cells(j, 16)).Interior.ColorIndex = RGB(221, 235, 247)

            Else

                Range(Cells(j, 1), Cells(j, 16)).Interior.ColorIndex = RGB(255, 255, 255)

            End If

        Next j



Application.ScreenUpdating = True



End Sub

Many thanks for your assistance!

You've mixed up Color, which you would use the RGB function with, and ColorIndex.

The RGB function returns a Long whole number corresponding to the color value. Specifically in this case,

• 
  RGB(221, 235, 247) returns 16,247,773
  


• 
  RGB(255, 255, 255) returns 16,777,215
  

Both of these are outside the very narrow range of possible values for a color index in the current color palette, hence the subscript out of range.

Change each instance of ColorIndex to Color.