Excel VBA Find Zero Value (Shows up as hyphen)

[yusunghee]

Hi Guys,

I'm really stuck here trying to figure this out.

I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.

The issue is that when I'm trying to write a macro that highlights all values that are zero (hyphen), the find code can't find it. I'm clueless on what to do.

Please help. Below is a strip down version of the code. Thanks!

Please Login or Register  to view this content.

foundcell brings up nothing, even if there's zeros in the column.

[Winon]

Hello yusunghee,

Your Code is looking for "Nothing".

Please try it this way:

Please Login or Register  to view this content.

[yusunghee]

Hi Winon,

Tried it still no luck. Attached is a sample of worksheet and macro.

Thanks.

[Winon]

Hello yusunghee,

See the attached sample WorkBook, which in my opinion, gives you what you want with much more flexability.

[Winon]

Hello yusunghee,

So my first attempt failed.

I accepted it as a challenge, and came up with Code (not very elegant) but it does what you want, and more.

New sample WorkBook is attached, for your consideration.

Also see the "Please consider" note at the bottom of this post.

[yusunghee]

Unfortunately, i'm still having no luck with this issue.

The issue is the format of the cell. In that attached worksheet, which is winon's code, i inserted two formulas in cell c20 and c21. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell highlights.

My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. Anyone know what the issue is and how i can find / highlight the zero value thats in an accounting format?

Thanks.