Link to home
Create AccountLog in
Avatar of Rayne
RayneFlag for United States of America

asked on

Formatting Challenge 101

Hello,
Formatting Qs – if user enters a ‘$’ in the cell L3, it look up all the cells in the active sheet and then changes them to a dollar format.
if user enters a ‘nm’ in the cell L3, it look up all the cells in the active sheet and then changes them to a  number format.
How  can this be done wth vba or any other manner possible

thank you
fommattingQuestion.xlsx
Avatar of Rayne
Rayne
Flag of United States of America image

ASKER

Not sure which way this could be done the easiest way possible…..
Avatar of Rayne

ASKER

One way I was thinking would be to track what is the current format (store it in a cell or something  and then when user inputs a % in L3, the code goes in and changes all the cells that had a dollar format to now show as percentage….
Why don't you put some code in the worksheet_calculate event.

If the selected cell is not L3, exit the sub

Otherwise, change the range of cells to whichever type is indicated.

Will the range of cells that are numbers change? Or will it always be those cells?

I can help you write the code if you want.
SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This VBA should do what you are after. It needs to be placed into the the sheet you want it to run on. I have attached a blank workbook with the code in pklace for you.

Michael

Private Sub Worksheet_Changed(ByVal Target As Range)
   If Intersect(Target, Range("L3")) Is Nothing Then Exit Sub
   
   Dim c As Range
   
   If UCase(Range("L3").Value) = "%" Then
      For Each c In UsedRange
         c.NumberFormat = "$#,##0.00"
      Next
   Else
      For Each c In UsedRange
         c.NumberFormat = "0.00%"
      Next
   End If
End Sub

Open in new window

Example.xlsm
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Rayne

ASKER

Hello All,

Thank you for your feedback, Yes if someone can assist with VBA, that would be awesome!!

Etech0 - Otherwise, change the range of cells to whichever type is indicated. – Sure this will work
Will the range of cells that are numbers change? Or will it always be those cells? – the range can change that’s why I need something that’s dynamic and catches any cell that has the target format to change from.
Sure VBA code will work great

Brian – Worksheet Change will work as well

I am open to any way that works awesome
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Rayne

matthewspatrick's example is far more efficient than mine and I would go with that one myself

Michael
Avatar of Rayne

ASKER

Awesome Guys!!

Question to Michael ::
"Brians example is far more efficient than mine and I would go with that one myself"

Are you referring to RedmondB or  matthewspatrick....I am confused :(
Rayne,

Thanks for thinking of me, but I didn't give an example so matthewspatrick's the one to go with!

Regards,
Brian.
Avatar of Rayne

ASKER

Perfect
Avatar of Rayne

ASKER

Sure Brian, no problem :)
Avatar of Rayne

ASKER

Hello Guys,

I have this question opened for 5 days. No one replied. Maybe you might have any answer?
https://www.experts-exchange.com/questions/27927126/Sort-it-in-a-certain-Way-Improvment-if-any.html

tHank you

Rayne
Avatar of Rayne

ASKER

Thank all!!