So, in cell A1, the user may enter "KG" or "LBS" (data validated).

In cell A2, the user enters a number.

And in cell A3 a calculation is done and displayed using a formula.

I know I can use a custom number format of 0 "KG" to display KG as text next to the number, and 0 "LBS" to display LBS.

Is it possible to have some conditional number format to display "LBS" or "KG" in A2 based on the value in A1?

I can't use a formula in that cell because the user needs to enter the required data in that cell. In essence, if conditional formatting had a number format section, I could do this pretty simply. I'm on a Mac, so maybe Excel on Windows has this option?

I assume this can be done easily in VBA, but I'm wondering if this can be done without VBA, and how if so.


this should work for you, press ALT + F11 and paste this in, When ever A1 is changed the number format will change in A2

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        With Range("$A$2")
            Select Case Target.Value
Case "KG":
                .NumberFormat = "###""kg"""
Case "LBS":
                .NumberFormat = "###""lbs"""
            End Select
        End With
    End If
End Sub
| improve this answer | |
  • @Sean no worries, if it works, accept it as the correct answer for others to see and hopefully use – PeterH Oct 5 '17 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.