Formatting a cell based on another cells value

[b o b]

hello-

I would like to format a cell number type based on another cells value.
For instances I have two columns A and B. Column A is a list of
numbers and column B is a list of units. Depending on what type of
unit is enter in column B, I would like excel to adjust the type of
number in column A. The example below shows a two decimal place number
for units of Amps and a fraction for units of HP. I do not what to
have to manually format column A to accomodate for different types of
units.

ex: A B
10.25 Amps
1/3 HP

 

[Ken Johnson]

hello-

I would like to format a cell number type based on another cells value.
For instances I have two columns A and B. Column A is a list of
numbers and column B is a list of units. Depending on what type of
unit is enter in column B, I would like excel to adjust the type of
number in column A. The example below shows a two decimal place number
for units of Amps and a fraction for units of HP. I do not what to
have to manually format column A to accomodate for different types of
units.

ex: A B
10.25 Amps
1/3 HP

Hi b o b,

I think you have to use a Worksheet_Change Event Procedure, like the
following which will set the format of a column A cell when the unit is
entered into the column B cell in the same row...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(2)) Is Nothing Then
Select Case Target.Value
Case "HP"
Cells(Target.Row, 1).NumberFormat = "# ?/?"
Case "Amps"
Cells(Target.Row, 1).NumberFormat = "0.00"
Case Else
Cells(Target.Row, 1).NumberFormat = "General"
End Select
End If
End Sub

To get the code in place...

1. Copy it here
2. Go back to your worksheet and right click its tab.
3. Select "View Code" from the popup menu
4. Paste the code into the sheets code module
5. Press Alt + F11 to get back to the worksheet
6. Save

Ken Johnson