Testing for a #VALUE!

[sjw62]

Hi,
I am trying to code a loop that tests a column of numbers (some cells contain #VALUE! errors). I want the loop to identify the cells with the #VALUE! in it and then make the cell yellow until it reaches the end of the data set.
The code i have written so far can identify and make yellow any of the numbers in the data, however when a #VALUE! is added and i try to make it yellow i get a run time error 13 type mismatch.
Here is the code i have:

Please Login or Register  to view this content.

[rylo]

Hi

How about

Please Login or Register  to view this content.

rylo

[downforce]

1. You wanna be putting that code in the:

2. You can do it either by:

Please Login or Register  to view this content.

in your macro or:

or the other option is to use Conditional Formating within Excel itself.
Format -> Conditional Formatting.

Change "Cell Value Is" to "Formula Is" then add =ISERROR(cellref) e.g. in E4 put =ISERROR(E4).

Copy and paste and your away.

EDIT: Damn beaten by rylo!

[sjw62]

Thanks for you response, Rylo i got your code to work.
What if i want to change the cell value to 0, as opposed to yellow?

[rylo]

Hi

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

rylo

[sjw62]

thanks for your help, much appreciated

[royUK]

Why not fix the errors?