Thread: Macro to format columns based on cell value

If I were working on Excel 2007 I think I would know how to do this but since I am on 2003 I am hoping someone can tell me if this can even be done.

Premise:
I have a "Value" Sheet and depending on whether the currency value is USD or GBP I would like Columns W thru BF, BI,BK,BM,BO,BQ,BS,BU to reflect the appropriate symbol.

USD = $
GBP = £

The cell that dictates what the results will be is $I$3

Can this be done?

My VBA skills are improving but as my moniker suggests, I am "Guru in Training" aka "VBA Newbie"!

Also, can anyone tell me why all of a sudden I can't assign Macros to "buttons"? Is there some sort of option that needs to be reset?

All help is greatly appreciated!

This workshett Change event handler will change the formats of your columns whenever the contents of I3 is changed. Note it only works for the values you provided. The format strings I got from Format->Cells Number:Accounting. I recorded a macro to reveal them.

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "$I$3") Then
    If (Target.Value = "GBP") Then
        Range("W:BF,BI:BI,BK:BK,BM:BM,BO:BO,BQ:BQ,BS:BS,BU:BU").Cells.NumberFormat = _
            "_-[$£-809]* #,##0_-;-[$£-809]* #,##0_-;_-[$£-809]* ""-""_-;_-@_-"
    ElseIf (Target.Value = "USD") Then
        Range("W:BF,BI:BI,BK:BK,BM:BM,BO:BO,BQ:BQ,BS:BS,BU:BU").Cells.NumberFormat = _
            "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
    End If
End If
    
End Sub

Thank you for helping with this, I copied/pasted the code but when I try to run it I get nothing and I can't even find the macro in the list. And for some reason, I used to be able to assign macros to buttons but now I can't. Is there a bigger issue that I need to resolve in order to go forward?

This is not a macro in the sense of executing it by name or button. It is an event handler for the worksheet. It should be place in the Value sheet object in VBA. Each time you make a change on that worksheet, this module will run. It only affects the worksheet when you change the currency field on the Value sheet. If you put it in Module1 of the workbook, move it to the Value worksheet object.

Does it matter that $I$3 is a formula cell that derives it's information from another page?

It shouldn't. The Change event handler is invoked whenever calculationis required. Are you using automatic calculation? If you can, send me your workbook with the forumal and the the event handler in and I'll take a look.

Ok Newbie alert! Ummmm. how do I "move it to the Value worksheet object". I know how to add modules but I am not sure how to do what you suggested. I have attached file. I learn by example but am primarily a visual picture kind of gal. Word problems suck for me.

Sorry to confuse you. I have made the move in the attached file. When you are in VBA you will notice in the Project Browser where you have added Module1. Well above that are the Microsoft Excel Objects. If you double click on the one labeled Sheet3 (Value) you will see where I put the routine. When you are working with worksheet event handlers, that is where they go. Each worksheet has its one event handlers. The same is true of thisWorkBook, which should contain workbook event handlers (like Open).

If you add a new worksheet to your workbook while in Excel you will see that a new Sheet object is created in the Microsoft Excel Objects.

Keep using your Guru skills at learning
Full%20Scope%20Template(1).xls

Awesome and I double checked to see how I would do it and works perfectly! Yay!! Learned a cool new toy! You wouldn't by any chance know why I all of sudden can't add buttons and assign macros would you?

What kind of buttons are you talking about? Toolbar buttons, or Control buttons (created from the Control Toolbox). You can also assign macros to shapes.

I had been using the "buttons" from the Control Toolbox. I select the Command Button, place it on the worksheet and then try to "Assign Macro" but the option is not available. I can't even seem to rename it! I could have sworn that was what I was doing previously and never had a problem before. I have a couple of macros I want to assign to the Template once information has been populated and don't know if I "switched" something off by accident?

Figured out what my problem was!! I need to use the "FORMS" Toolbar, NOT the Control Toolbox. Thanks so much for your awesome help 5 gold stars for you!!