Private Sub - Invalid or Unqualified Reference... But where?!?

[ClimoC]

Hey

Got this code returning the Invalid or Unqualified error.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AS6:BX500")) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Set MyGrid = Range("AS6:BX500")
    For Each Cell In MyGrid
        If Cell.Value = "" Then
            .ColorIndex = 1
            .Pattern = xlGrid
            .PatternColorIndex = 51
        End If
        If Cell.Value = "D/L" Then
            Cell.Interior.ColorIndex = 41
        End If
        If Cell.Value = "Last Chance" Then
            Cell.Interior.ColorIndex = 48
        End If
        If Cell.Value > "0.0000001" And Cell.Value < "1000000000.00" Then
            Cell.Interior.ColorIndex = 5
 
 
        If Cell.Value <> "" And Cell.Value <> "D/L" And Cell.Value <> "Last Chance" And Cell.Value < "0.0000001" And Cell.Value > "1000000000.00" Then
            Cell.Interior.ColorIndex = 3
        End If
    Exit For
 
    Next
Application.EnableEvents = True
Application.ScreenUpdating = False
End Sub

It highlights the first formatting bit ".ColorIndex = 1" for the first statement, "If Cell value is blank ("")"

Can anyone help me on this? I don't see what the problem is with it...

Cheers
C

 

[Hermanito]

Try this instead:
Cell.Interior.ColorIndex = 1

You are using . notation but you are not using a with-block
You can do this:

With Cell
    .Interior.ColorIndex = 1
End With

 

[ClimoC]

Oh ok thanks - that seemed to fix it.

Next question now that that's been resovled:

I get 'Next without For'....

There is a For!

If I remove the Next, I get

"Block If without End If"

??????

 

[Hermanito]

Remove the line with 'Exit For'

Also check if there is an End If after this:

If Cell.Value > "0.0000001" And Cell.Value < "1000000000.00" Then
            Cell.Interior.ColorIndex = 5

If these two lines are actually one line in the editor, the End If is not needed...

 

[ClimoC]

Ok, code now looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AS6:BX500")) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Set MyGrid = Range("AS6:BX500")
    For Each Cell In MyGrid
        If Cell.Value = "" Then
            Cell.Interior.ColorIndex = 1
            Cell.Interior.Pattern = xlGrid
            Cell.Interior.PatternColorIndex = 51
        End If
        If Cell.Value = "D/L" Then
            Cell.Interior.ColorIndex = 41
        End If
        If Cell.Value = "Last Chance" Then
            Cell.Interior.ColorIndex = 48
        End If
        If Cell.Value > "0.0000001" And Cell.Value < "1000000000.00" Then
            Cell.Interior.ColorIndex = 5
        End If
        If Cell.Value <> "" And Cell.Value <> "D/L" And Cell.Value <> "Last Chance" And Cell.Value < "0.0000001" And Cell.Value > "1000000000.00" Then
            Cell.Interior.ColorIndex = 3
        End If
    Next
    
    
Application.EnableEvents = True
Application.ScreenUpdating = False
End Sub

And now it highlights the last line "End Sub" and says "Block If without End If"

I've double checked it and I can't see where it needs to end an if statement

 

[ClimoC]

Never mind - found it. It was the first " If Not "

 

[Hermanito]

I missed that one too, I advise you to use proper indentation of your code to easier spot such problems...