Change Cell Number format based on a value

[Dokat]

Hi,

I have a dynamic table with index formula. I need number format of the cell to match the reference cell or be able to change it based on a value on another cell.

Is it possible to change the number formatting without VBA code?

Thanks

 

[r2c2]

Sure, you can use conditional formatting. It depends on the version of Excel you have, but anything beyond 2010 should allow you to define rules based on conditions with formats as you want. If you are not clear, share an example workbook and I can show you how to set up the rules.

 

[Dokat]

My worksheet has confidential informationi cant share it but what i want to do is if the value in D1 =1 then D4:D11 Format "$0.0,,"M"
if the value in D1 =2 then D4:D11 Format "0.0%"
if the value in D1 =3 then D4:D11 Format "0.0"

Does this help?

Thanks

 

[bosco_yip]

Please check with CF formulas as per attachment

Regards
Bosco

 

[Belleke]

Hi Dokat,

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case [D2]
        Case 1
        Range("D4:D11").NumberFormat = "$0.0,,"
        Case 2
        Range("D4:D11").NumberFormat = "0.0%"
        Case 3
        Range("D4:D11").NumberFormat = "0.0"
End Select
End Sub

 

[Dokat]

Hi Belleke,

Thank you for your response however code didnt work.

 

[Dokat]

Please check with CF formulas as per attachment

Regards
Bosco

Hi Bosco,

Thank you it worked however I have a chart that reference data in D4:D11 and number formatting on the axis doesnt change with the source data.

Thanks

 

[Belleke]

Thank you for your response however code didnt work.

It does what you asked, see attached.