Search code examples
excelcell-formattingvba

Number format of a cell based on info given on an adjacent cell (VBA)

Trying to set the number format of a cell based on the currency selected on an adjacent cell using VBA. See sample below.

enter image description here

So far I am using the below code but I cannot seem to make the format to appear properly

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim Rng As Range
Dim ccy As String

ccy = Range("A3").Value
pctFormat = "0.000%"
fxFormat = ccy + " " + pctFormat


If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
    If Target.Value <> preValue And Target.Value <> "" Then
        Application.EnableEvents = False

  Cells(Target.Row, Target.Column + 1).NumberFormat = fxFormat

        Application.EnableEvents = True

    End If
End If

On Error GoTo 0
End Sub
Solution

  • you could try this option to set your fxFormat variable:

    fxFormat = "[$" & ccy & "] 0.000%"