Automate number of decimal places

[scouse13]

Hi All

I have 2 sheets in a workbook (may add more), most cells for entering numbers are set to NO decimal places, another 4 cell on each row are set to convert figures and show the result to 4 decimal places, these have all been set and all works fine. but there will be occasions when the cells displaying to 4 decimal places, will need to be changed to 5 decimal places, can this be done via VBA, I don't really have to go through all the cells and change them manually if it can be done using vba


many thanks for any help


scouse13

[jolivanes]

Deleted.
Code changed the non formatted cells also.

[MrShorty]

Depending on exactly what determines the decision to be 4 or 5 decimal places, I doubt VBA is required for this. A lot depends on how you are deciding when to display 4 and 5 digits, which your OP does not explain.

Custom number formatting can handle limited conditions -- for example, if "smaller" numbers should have 5 digits and "larger" numbers should have 4 digits, with a clear demarcation between "small" and "large". See this help file: https://support.office.com/en-us/art...2-09fab54be7f4

More elaborate conditions can be handled using conditional formatting: https://support.office.com/en-us/art...c-65d913033d18 Usually the hardest part is developing the conditions or formula that tests the conditions as part of the conditional formatting.

If your conditions for choosing which format to apply are extremely complex or difficult to formulate, then VBA may be preferred. IMO, it seems that your logic must be pretty convoluted not to fit into the "use a formula to decide what formatting to apply" part of conditional formatting.

Which approach would you like to use?

[scouse13]

hi mrshorty

thanks for your reply, the people using the spreadsheet will decide whether 4 0r 5 decimal places should be used as it will depend on the level of accuracy for the converted cells, I was thinking of maybe an option button or checkbox when set to true could change the relevant cells from 4 to 5 places

scouse13

[jolivanes]

This will toggle between 4 and 5 decimals. Don't know how "fast" it is though.

Please Login or Register  to view this content.

[scouse13]

thanks jolivanes, before I try it will this change all the cells to this format, as it is only certain cells I need changing

scouse13

[jolivanes]

If you want to change only certain cells, we need to know the sheet names or sheet index numbers and the cell addresses that need changing.

BTW, you should always try macro suggestions on a copy of your original any way. Never on the original.

Re: "depend on the level of accuracy for the converted cells"
Accuracy for calculations is not affected by decimals showing.
What you see in the cells is strictly visual.