decimal points

[dandedo2]

how do I set up cells so that it would be, for example,

if value > 100 round to nearest whole number
if value< 100 use full number

I have 2 sets of data that are pulled into certain cells, one set is numbers under 5, say 1.2345 which I want expressed that way, whereas the other set is numbers such as 2030.8 which I want simply rounded to the nearest number.

Thank you

[will1128]

[vba]Public sub Number_Format()
Dim ws As Worksheet
Dim c As Range

For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
Select Case c.value
Case c.value > 100
c.NumberFormat = "0"
Case c.value < 100
c.value = c.value
Case Else
c.NumberFormat = "0.000000" 'number fo decimal places to go to
End Select
Next c
Next ws
End Sub[/vba]

[dandedo2]

thats brilliant.

where would I want to paste this so that it only impacts the top sheet in my workbook? I'm not so hot with vba, normally try to keep it to formulas/

Thank you

[will1128]

Change

set c = Range("value":"value")

I don't know what the top sheet of yoru workbook is.

but you would need to define the range.

C = Range("A1:F5") would search through A1 to F5