| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
colore cell based on condition
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 :) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
colore cell based on condition
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) "gaba" wrote in message ... 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 :) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
colore cell based on condition
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" wrote: 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) "gaba" wrote in message ... 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 :) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
colore cell based on condition
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) "gaba" wrote in message ... 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" wrote: 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) "gaba" wrote in message ... 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 :) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
colore cell based on condition
Thanks so much, Bob. It works great!
"Bob Phillips" wrote: 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) "gaba" wrote in message ... 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" wrote: 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) "gaba" wrote in message ... 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 :) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
colore cell based on condition
That's good, sorry about the inadequate testing first time.
Bob "gaba" wrote in message ... Thanks so much, Bob. It works great! "Bob Phillips" wrote: 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) "gaba" wrote in message ... 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" wrote: 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) "gaba" wrote in message ... 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 :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lock cell based on a condition | Excel Worksheet Functions | |||
| Condition based on cell colour | Excel Worksheet Functions | |||
| Is there a way to delete a cell value based on a condition? | Excel Discussion (Misc queries) | |||
| Fill a cell based on a condition being met | Excel Worksheet Functions | |||
Hybrid Mode
