runtime error 9 - Subscript Out of Range Error or runtime 1004 - method 'range of object' worksheet failed when trying to color cell

[GlennW81]

Hi,

I have a vba code that is intended to highlight cells based on value of the cell i.e. if it is empty. The code identifies the cell values correctly but it will not colour the cell.
I keep getting an runtime error 9 - Subscript Out of Range Error or runtime 1004 - method 'range of object' worksheet failed.
I have completed other searches and cannot seem to find the solution. As I can clearly see that the cell I am pointing to exists. I have even done a test with a msgbox for that cell and it shows that it exists.

The following is the code. whenever the if statement is met I get the runtime error:

For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "raw data" Or ws.Name = "HP dealer permits" Or ws.Name = "HP harvester permits" Then
        'do nothing
        Else
        lastrow2 = ws.Range("B" & Rows.Count).End(xlUp).Row 'this finds the last row so that this data can be pasted in the same sheets as previous filtered data
        For i = 2 To lastrow2
        
            If ws.Range("D" & i).Value = "no match" Then ws.Range(ws.Cells(i, 2), ws.Cells(i, 4)).Interior.ColorIndex = 3
            If ws.Range("E" & i).Value = "#NA" Then ws.Range(ws.Cells(i, 5)).Interior.ColorIndex = 3
            If ws.Range("J" & i).Value = "No" Then ws.Range(ws.Cells(i, 8), ws.Cells(i, 11)).Interior.ColorIndex = 3
            If IsEmpty(ws.Range("L" & i)) = True Then ws.Range(ws.Cells(i, 12)).Interior.ColorIndex = vbRed
            If IsEmpty(ws.Range("N" & i)) = True Then ws.Range(ws.Cells(i, 14)).Interior.ColorIndex = 3
       Next i
Next ws
End sub

Regards,
Glenn

 

[mikerickson]

Regarding the line If ws.Range("E" & i).Value = "#NA" Then ws.Range(ws.Cells(i, 5)).Interior.ColorIndex = 3 :
What is in ws column E. As written it should be a range address.

 

[Peter_SSs]

There are a couple of issues that I can see.

1. ws.Range(ws.Cells(i, 12)) should be just ws.Cells(i, 12)
   Similar for other code lines with such structure
   
   
2. vbRed is not a ColorIndex. You need to either change vbRed to to a ColorIndex Number (1 - 56) or else change ColorIndex to Color

 

[Alex Blakenburg]

This might keep things moving a bit:
(Ideally we would pull the "ws" out into a With ws .... End With structure.)

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "raw data" And ws.Name <> "HP dealer permits" And ws.Name <> "HP harvester permits" Then
        lastrow2 = ws.Range("B" & Rows.Count).End(xlUp).Row 'this finds the last row so that this data can be pasted in the same sheets as previous filtered data
        For i = 2 To lastrow2
            If ws.Range("D" & i).Value = "no match" Then ws.Range(ws.Cells(i, 2), ws.Cells(i, 4)).Interior.ColorIndex = 3
            If Application.IsNA(ws.Range("E" & i)) Then ws.Cells(i, 5).Interior.ColorIndex = 3
            If ws.Range("J" & i).Value = "No" Then ws.Range(ws.Cells(i, 8), ws.Cells(i, 11)).Interior.ColorIndex = 3
            If IsEmpty(ws.Range("L" & i)) = True Then ws.Cells(i, 12).Interior.ColorIndex = 3
            If IsEmpty(ws.Range("N" & i)) = True Then ws.Cells(i, 14).Interior.ColorIndex = 3
       Next i
    End If
Next ws

 

[GlennW81]

Thanks @Alex Blakenburg , The reason the ws is not in a With ws ..End With, is because I need it to loop through and check each ws. and am not familar if using a loop with the With ..End With function.
Thanks @Peter_SSs , I had previously tried it by removing the 'ws.range' but still had the runtime error 9 - Subscript Out of Range Error occurring. Though I believe now this was because of the vbRed statement causing the runtime error 9. I trialled the vbRed when I thought the '3' was not working. I got the vbRed from Background color in a range of cells and Coloring a range of cells (VBA) which used the vbRed and vbWhite statement in their code.

I have removed the ws.Range when only referring to one cell, and only using the numbers for the colour index. This seems to be working now. Thank you all helping to identify the issue.

 

[Peter_SSs]

Glad that you have it working now. Thanks for letting us know.

 

[Alex Blakenburg]

If you haven't used the code I proved I suggest you test out your #N/A line, my expectation is that it will error out.