Conditional formatting: round to the first non-zero decimal place

Well, that's frustrating. I guess the only other option would be to use conditional formatting to display the text "<0.1" when this occurs.

This code will perform your specified formatting on any cell that changes and has a numeric value <0.1. That might not be exactly what you want depending on your data layout but it gives you the idea of what can be done.

Please Login or Register  to view this content.

Hi 6StringJazzer, thanks for your amazing help so far!!!


So, I've been working on this on my end as well, and still haven't come to any definite solutions. In the process of working with this, however, I realized I'll have to add another rule. Here are the four rules so far:
1. -456123.084356 --> -456,123.1
2. -0.084356 --> -0.01
3. 0.084356 --> 0.01
4. 456123.084356 --> 456,123.1

It's rule number 2 that I didn't realize I would need up to now. I'm just starting out with VBA, but will take your code and see if there's anyway that I can change this myself.

Here is an update to deal with negative numbers

Please Login or Register  to view this content.

It sounds like the numbers you want to format are resulting from formulas. Worksheet_Change does not recognize when the result of a formula changes, only if a value changes or if the actual formula changes. There are a couple of ways to deal with this but I would need to see your file. Can you attach it?

Also, some cells aren't updating and after clicking the individual cells and refreshing, the links in these cells to the other workbook just disappear.

And I don't understand at all what you mean by this. What "just disappears"?

Originally Posted by Kabouterke

I've put this code into each individual tab's VBA window....

That will work but you have to make multiple copies of the same code that way. I didn't know you wanted to use it in multiple worksheets. If you attach your file I can show you how to restructure to put the code in one place and call it from worksheet module.

I got the files. I thought it would be obvious from looking at the data, but it's not: Which data needs to be rounded? You have a LOT of data in there.

Also, all of the data in the target file is links to the other file. That means that Excel can't readily determine when the values are changing. There are a couple of options:

1. Put a button the file to run the macro to update formatting on all desired data
2. Use the Macro dialog box to run the macro when desired