Format column to have different decimal places

[Guest]

On my spread sheet I have a column with different equipment numbers. If the
number starts with 25 it needs to have 4 decimal places (25.4444) if it
starts with a 26 it needs to have 3 decimal places (26.333). Not sure how to
do this we tried conditional formatting and it does not give decimal as
option. We tried to format as general but it would drop the zero off the end.
Example 26.330 would be 26.33.

 

[Anne Troy]

Sounds like you'll need a helper column, or you'll need a worksheet change
event. Which are you open to? Extra column or macro?
*******************
~Anne Troy

www.OfficeArticles.com

 

[STEVE BELL]

Let's use Range(A1:A15). The trick is defining x as an Integer.
(you could also adapt this to a worksheet change event)


Dim x As Integer, cel As Range

For Each cel In Range("A1:A15")
If IsNumeric(cel) = True Then
x = cel
If x = 25 Then
cel.NumberFormat = "#,##0.0000"
ElseIf x = 26 Then
cel.NumberFormat = "#,##0.000"
End If
End If
Next

 

[Guest]

Branden,

Here is a Worksheet_Change event version.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo BadTarget
If Target.Column = 1 Then ' 1 = column A - adjust for your specific column
If IsNumeric(Target) And Target >= 25 And Target < 26 Then
Target.NumberFormat = "0.0000"
Else
If Target >= 26 And Target < 27 Then
Target.NumberFormat = "0.000"
End If
End If
End If

BadTarget:
On Error GoTo 0

End Sub

Roy

 

[Norman Jones]

Hi Steve,

The trick is defining x as an Integer.

This results in rounding to the nearest integer. In consequence, all
numerical values between (and including) 25.5 and 26 are rounded to 26 an
formatted accordingly.

Similarly, values between (and including) 26.5 and 27 are rounded to 27 and
will not, therefore, be formatted by your procedure.

It would be better, IMO, to adopt the appoach suggested by Roy or to use the
VBA Int function or, if negative values were possible, the Fix function.

 

[STEVE BELL]

Norman,

Thanks for the correction. (I should have tried it out first)

Did find the following to work. Incremented a list from 25 - 27 by 0.1

Dim x As Integer, cel As Range
For Each cel In Range("b1:b25")
x = WorksheetFunction.RoundDown(cel, 0)
cel.Offset(0, 1) = x

Next

 

[Guest]

Thanks for all the help guys got it to work, I really appreciate it.