colore cell based on condition

[Guest]

hi everybody,
It is possible to change the color index of a cell with error 2015 (#VALUE!)?
if I get this error-cells colored with orange, then I can check the values
of the rest, somehow is stopping on the ones with errors. All cells I'm
checking are colored yellow, I'm ignoring the rest.

Thanks for any help

sub CheckValue ()

For Each c In Range("E17", "CE44")
If c.Interior.ColorIndex = 6 And _
c.Value = CVErr(xlErrValue) Then
c.Interior.ColorIndex = 44

ElseIf c.Interior.ColorIndex = 6 And _
c.Value > 9999 Then
c.Interior.ColorIndex = 44
c.FormatNumber = "0.00"
End If
Next



End Sub

gaba

 

[Bob Phillips]

Gaba,

Your problem is evaluating for an eror when there is not one there. Try
this

On Error Resume Next
For Each c In Range("E17", "CE44")
If c.Interior.ColorIndex = 6 Then
If c.Value = CVErr(xlErrValue) Then
If Err.Number = 0 Then
c.Interior.ColorIndex = 44
End If
ElseIf c.Interior.ColorIndex = 6 And _
c.Value > 9999 Then
c.Interior.ColorIndex = 44
c.FormatNumber = "0.00"
End If
End If
Next
--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob, thanks for your answer. I've tried the code and is not catching the
numbers bigger than 9999... Soon as the value is an error quits. Any ideas?
Gaba

 

[Bob Phillips]

Gaba,

Is this better?

On Error Resume Next
For Each c In Range("E17", "CE44")
If c.Interior.ColorIndex = 6 Then
If c.Value = CVErr(xlErrValue) Then
If Err.Number = 0 Then
c.Interior.ColorIndex = 44
ElseIf c.Interior.ColorIndex = 6 And _
c.Value > 9999 Then
c.Interior.ColorIndex = 44
c.FormatNumber = "0.00"
End If
Err.Clear
End If
End If
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks so much, Bob. It works great!

Gaba,

Is this better?

On Error Resume Next
For Each c In Range("E17", "CE44")
If c.Interior.ColorIndex = 6 Then
If c.Value = CVErr(xlErrValue) Then
If Err.Number = 0 Then
c.Interior.ColorIndex = 44
ElseIf c.Interior.ColorIndex = 6 And _
c.Value > 9999 Then
c.Interior.ColorIndex = 44
c.FormatNumber = "0.00"
End If
Err.Clear
End If
End If
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

That's good, sorry about the inadequate testing first time.

Bob