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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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.
>>
>>
>
>

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.