Format cell as Currency or Number based on value of another cell

[CDP]

How can I format a cell to be either currency or a number, based on another cell value? For example, if A1="BP", then B1 should auto format as a Number when a value is entered. However, if A1="Flat fee", then B1 should auto format to be Currency when a value is entered. Cell A1 (whole column actually) is a drop-down list with only these 2 possible options. Is this possible to make B1 auto format based on the value of A1? Thanks!!!

 

[AlphaFrog]

Right-click on the sheet tab that you want this to work on and select "View Code" from the pop-up menu. Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cel As Range
    
    If Not Intersect(Target, Columns("A:A")) Is Nothing Then
    
        For Each cel In Intersect(Target, Columns("A:A"))
        
            If cel.Value = "Flat fee" Then
                cel.Offset(0, 1).NumberFormat = "$#,##0.00"
            Else
                cel.Offset(0, 1).NumberFormat = "General"
            End If
                
        Next cel
    End If
End Sub