Ignoring #value in a macro

[Alpacino]

Hi everyone I have a marco that colors the interior a cell depending on it's value.
Eg cell value > 65 and < 85 then vbblue
cell value > 65 then vb green
Cell value > 35 and < 65 then vbYellow
Cell value < 35 then vbRed

This works fine However is the cell has #value it's turns the cell blue?
I dont understand why?

Any ideas

 

[Joe4]

Can you paste your code?

 

[Alpacino]

Dim cell as range

For each cell in range("B3:B28")
If iserror(cell.value) then
Cell.interior.colorindex =xlNone
EndIf
If cell.value > 45 then
Cell.interior.color = vbGreen
EndIf
If cell.value < 45 then
Cell.interior.color = VbRed
EndIf
Next cell

 

[Joe4]

I think you left some of the necessary code out. I do not see anything in that code setting any cells to the color blue.

If you want us to try to figure out what is going, we really need to see all of the pertinent code.

 

[Jonmo1]

After your set the colorindex to xlnone for errors, It still procedes to test if the cell >45, type mismatch.

You have to structure your if's in a way that it no longer tests for >45 or <45 when it's an error..

Try

Dim cell As Range
For Each cell In Range("B3:B28")
    If IsError(cell.Value) Then
        cell.Interior.ColorIndex = xlNone
    Else
        If cell.Value > 45 Then
            cell.Interior.Color = vbGreen
        End If
        If cell.Value < 45 Then
            cell.Interior.Color = vbRed
        End If
    End If
Next cell

Hope that helps.