Macro to Conditionally Format

[atgordon]

I was hoping someone could help me construct a short macro. I have a range of cells on a worksheet (B5:Y40). I want the macro to look at each of the cells in that range and if the absolute value is between 0 and 1, it formats the cell as #,##0.00. If the absolute value is greater than 1, it formats the cell as #,##0. I also want to include some sort of IsError statement or comparable that will exit the macro if the cell contains an error of any type. I don't want this macro to automatically calculate, but rather only run when I manually do so.

Any help would be greatly appreciated.

Thanks.

 

[lenze]

Maybe something like this!!

Sub Doit()
Dim cl As Range
On Error Resume Next
For Each cl In Range("B5:Y40")
If Abs(cl) < 1 Then
    cl = Format(Abs(cl), "#0.00")
Else: cl = Format(Abs(cl), "#0")
End If
Next cl
End Sub

lenze

 

[atgordon]

Thanks - that is great. One more question - how would I add a condition so that if the abs value = 0, the cell format would be "-". Essentially, it would have 3 conditions - if the abs is less than 0, if it equals 0 and everything else. Thanks.

 

[atgordon]

Also, if possible, I don't want the cell to show the absolute value - for instance, if before the macro, the cell's value is -6, after running the macro, it is (6). I just mentioned absolute value as a way to avoid having to choose between 0 and 1 and between 0 and -1.

Thanks.

 

[lenze]

Give this a shot

Sub Doit()
Dim cl As Range
On Error Resume Next
For Each cl In Range("B5:Y40")
If cl = 0 Then
   cl = Format(cl, "-")
ElseIf Abs(cl) < 1 Then
    cl = Format(cl, "#0.00")
Else: cl = Format(cl, "#0")
End If
Next cl
End Sub

lenze