Format column to have different decimal places
 

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.

Format column to have different decimal places
 

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


"Branden" wrote in message
...
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.

Format column to have different decimal places
 

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

--
steveB

Remove "AYN" from email to respond
"Branden" wrote in message
...
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.

Format column to have different decimal places
 

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

"STEVE BELL" wrote:

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

--
steveB

Remove "AYN" from email to respond
"Branden" wrote in message
...
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.

Format column to have different decimal places
 

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.

---
Regards,
Norman



"STEVE BELL" wrote in message
news:iJXBe.81$N91.25@trnddc08...
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

--
steveB

Remove "AYN" from email to respond
"Branden" wrote in message
...
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.

Format column to have different decimal places
 

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

--
steveB

Remove "AYN" from email to respond
"Norman Jones" wrote in message
...
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.

---
Regards,
Norman



"STEVE BELL" wrote in message
news:iJXBe.81$N91.25@trnddc08...
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

--
steveB

Remove "AYN" from email to respond
"Branden" wrote in message
...
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.

Format column to have different decimal places
 

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

"Branden" wrote:

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.