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

GlennW 0
Viewed 8

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 
     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 = 3
     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

outofrangeexception compiler-errors background-color
1 Answers

The issue was using the range when referring to one cell and the use of vbRed which does not appear to function for colouring the cell.

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 https://p2p.wrox.com/excel-vba/72653-background-color-range-cells.html 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.

GlennW0
Related