"Subscript out of range" error when trying to change text color based on cells' values

Question

I am trying to create a macro that will compare the values of cells in two columns labeled "Requisition Number" and "PO #" to the cells in the previous row. If the cells are the same, I want the lines to be highlighted the same color. The following is my code:

Sub changeTextColor()

    Dim Color As Integer

    Color = 5

    'Get number of rows in the specified column
    RowsCount = Range("A1", Range("A1").End(xlDown)).Rows.Count

    Dim colReq As Range
Dim colPO As Range


With ActiveSheet.UsedRange.Rows(1)
    Set colReq = .Find(What:="Requisition Number", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set colPO = .Find(What:="PO #", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
End With


    'Select cell
    ActiveSheet.Cells(1, colReq.Column).Select

    'Loop the cells
    For x = 1 To RowsCount
        If (ActiveCell.Value = ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column).Value) And _
            ActiveSheet.Cells(ActiveCell.Row, colPO.Column).Value = ActiveSheet.Cells(ActiveCell.Row+1, colPO.Column).Value Then

                ActiveCell.EntireRow.Font.ColorIndex = Color
                ActiveCell.EntireRow+1.Font.ColorIndex = Color

        Else
            Color = Color + 5
        End If

        ActiveCell.Offset(1, 0).Select
    Next

End Sub

I am receiving the error "Subscript out of range" in the following lines of my code, and am not sure how to fix it?

           ActiveCell.EntireRow.Font.ColorIndex = Color
           ActiveCell.EntireRow+1.Font.ColorIndex = Color

You need to learn about debugging. Step through your code and check the values of your variables. You don't check if the values are found (colPO). If the active cell is row 1 it will error because there is no row zero. — SJR, Jul 20 '17 at 18:58
Are you positive there's a `colPO` range when you get to that line? Also, I **highly** suggest learning [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Jul 20 '17 at 18:58
You will get an error if you are on row 1, because `ActiveCell.Row - 1` returns a `0` and there is no row 0. — braX, Jul 20 '17 at 18:59
Thanks, I accidentally uploaded an older version of my code with ActiveCell.Row - 1 , but fixed it to be +1 instead. However, I am still getting a "Subscript Out of Range error". I am positive that colPO exist, and has value of 4 when I was going through it. — SomeUser, Jul 20 '17 at 20:37
_ActiveCell.EntireRow_ defines a **range**. you cannot add and integer value to it. — jsotola, Jul 20 '17 at 21:26
do not use `With ActiveSheet.UsedRange.Rows(1)` to search row #1. use this instead `With ActiveSheet.Rows(1)` now that i think about it ... `With ActiveSheet` should work — jsotola, Jul 20 '17 at 21:28

Jarom · Accepted Answer · 2017-07-20T19:51:02.940

This is mostly a syntax error. ActiveCell.EntireRow + 1.Font.ColorIndex = ColorDoesn't make sense to excel. It is the EntireRow + 1 that is causing the problem. You will have to use offset like I see you have used in a piece of the code below.

Try this:

            ActiveCell.EntireRow.Font.ColorIndex = Color
            ActiveCell.offset(1,0).Select
            ActiveCell.EntireRow.Font.ColorIndex = Color

Like @brucewayne said in his comment, you really want to avoid selecting/activating cells as much as possible. It slows your code and can be clunky.

Here is an article on how and why to avoid using select and activate https://www.excelcampus.com/vba/how-to-avoid-the-select-method/

One of the powerful features of for loops is that you can use the counter variable (in your case 'x') in the code to take place of the offset. I have taken out the select and activate in your code using a couple of different techniques. Notice how I use cells(x,col) in the loop to move down one cell for each iteration, and how I use cells(x + 1,col) to go one lower than the value of x. (be sure to replace "Sheet Name" with the actual name of your sheet before you test the code out)

Sub changeTextColor()

Dim Color As Integer
Dim colReq As Range
Dim colPO As Range

Color = 5

    'Get number of rows in the specified column
RowsCount = Range("A1", Range("A1").End(xlDown)).Rows.Count



With Worksheets("Sheet Name").UsedRange.Rows(1)
    Set colReq = .Find(What:="Requisition Number", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set colPO = .Find(What:="PO #", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
End With



    'Loop the cells
    For x = 1 To RowsCount
        If (Worksheets("Sheet Name").Cells(x, colReq.Column).Value = Worksheets("Sheet Name").Cells(x + 1, colReq.Column).Value) And _
            Worksheets("Sheet Name").Cells(x, colPO.Column).Value = Worksheets("Sheet Name").Cells(x + 1, colPO.Column).Value Then

                Worksheets("Sheet Name").Cells(x, colReq.Column).EntireRow.Font.ColorIndex = Color
                Worksheets("Sheet Name").Cells(x + 1, colReq.Column).EntireRow.Font.ColorIndex = Color
        Else
            Color = Color + 5
        End If
    Next

End Sub

Thanks so much, that helped a ton! It worked almost perfectly, and makes more sense that how I had mine assembled. — SomeUser, Jul 20 '17 at 20:41
I'm glad it helped. When you're new to VBA it is natural to want to select and activate everything because that is how we use excel. But learning how to get around that is going to the next level. Good Luck! — Jarom, Jul 20 '17 at 21:09

"Subscript out of range" error when trying to change text color based on cells' values

1 Answers1