Link to home
Create AccountLog in
Avatar of BrdgBldr
BrdgBldrFlag for Switzerland

asked on

Excel VBA code to change range number format if certain cell value changes

The code below was pasted into the sheet code (not module, not workbook code) of the respective sheet and should change the formatting if the value in cell AB11 is changed. If the value is equal to "JPY", the formatting should show no decimals, otherwise two decimals.

1) Somehow it is not working, how to fix it? What am I doing wrong?
2) Can the Target.Address (currently $ab$11) also be a named range? If yes, how to do it correctly - I couldn't manage it getting to work with a range name of that cell instead of address
Private Sub ChangeFrom2DigitsToZeroDigitsOrBack(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("ab11:ab11")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    If Target.Address = "$ab$11" Then
       If Target.Value = "JPY" Then
           Range("DecimalsChange").NumberFormat = "#,##0;-#,##0;-"
           ElseIf Target.Value <> "JPY" Then
            Range("DecimalsChange").NumberFormat = "#,##0.00;-#,##0.00;-"
         End If
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End If

End Sub

Open in new window

Avatar of SiddharthRout
SiddharthRout
Flag of India image

Why don't you want to store the same in Worksheet Change Event?

Sid
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi BrdgBldr,

As Sid suggested, you need to use the Worksheet_Change event.

You can use it to call your procedure when it triggers, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  ChangeFrom2DigitsToZeroDigitsOrBack Target
End Sub

Also, you can use a named range by just calling its name.

Eg. (Assuming your named range is called "KeyCells"):


Private Sub ChangeFrom2DigitsToZeroDigitsOrBack(ByVal Target As Range)
  If Not Application.Intersect(Range("KeyCells"), Target) Is Nothing Then
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
  
    If Target.Value = "JPY" Then
      Range("DecimalsChange").NumberFormat = "#,##0;-#,##0;-"
    Else
      Range("DecimalsChange").NumberFormat = "#,##0.00;-#,##0.00;-"
    End If
      
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
  End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  ChangeFrom2DigitsToZeroDigitsOrBack Target
End Sub

Open in new window

Avatar of BrdgBldr

ASKER

Perfect!
so my error was that
1) I did not name it "Worksheet_Change"
2) ????
thank you very much!
1) Yes
2)????

:)

Sid
@TramTrak: sorry, when I was allocating points, your response was not yet visible to me... you would have deserved a few points as well.... sorry!
@SiddharthRouth: what else was wrong with my code?
There was nothing 'wrong' as such with your code, I just shortened your code :)

Sid
:o))))