Thread: change currency formatting based on value of another cell

Hello,
I have a conditional formatting issue in Excel 2007.

I have a drop down list which shows 4 countries; UK, DE, FR and NL

I would like to change the format of a number of cells to £ or € dependent on what the dropdown box has chosen.

I have a number of vlookups, so it pulls the right amount, and the right SKU, but I cannot get it to change the formatting correctly. I have even tried to simplify it to say, if UK use £ and if <> UK then use €, but that doesn't change either. If I change it, so it colours red for UK, and clear for not UK, that works.

Can anyone help please?
Thanks, 17ND

Configuration: Windows 7 x64/Excel 2007 SP2/Dell Latitude E4300

Click GO ADVANCED and use the paperclip icon to post up your workbook. Make sure the cells you're wanting help with are evident.

1) open up the hardware sheet module and remove the code that is in there
2) paste in this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, [A1]) Is Nothing Then
Set RNG = Range("K114:L141")
    Select Case Target
        Case "UK"
            RNG.NumberFormat = "[$£-809]#,##0.00;[Red]-[$£-809]#,##0.00"
        Case Else
            RNG.NumberFormat = "#,##0.00 [$€-1];[Red]-#,##0.00 [$€-1]"
    End Select
End If
End Sub

3) Close the editor and save your sheet.

Now any changes to cell A1 will trigger the reformatting.

I don't see the problem you're seeing. I do see a problem with your formulas, this would make them not show errors and evaluate the currency type faster:

K114: =IF($A$1="","",VLOOKUP($C114,vLookupSW!$A$2:$M$30,LOOKUP($A$1,{"DE","FR","NL","UK"},{11,11,11,9}),0) )

L114: =IF($A$1="","",VLOOKUP($C114,vLookupSW!$A$2:$M$30,LOOKUP($A$1,{"DE","FR","NL","UK"},{10,10,10,8}),0) )

Copied down, this appears to make the sheet appear to function more smoothly. I can see no error with macro itself. Removing the value in A1 would cause the macro to do nothing, so the corrected formulas above might resolve your perceived issue.

The LOOKUP() formulas are alphabetical, and you could actually shorten them down to:
=IF($A$1="","",VLOOKUP($C114,vLookupSW!$A$2:$M$30,LOOKUP($A$1,{"DE","UK"},{11,9}),0))

...but I thought it might be confusing to not see the FR and NL values listed, even though they aren't needed since they are the same value as DE.

Why did you move the sheet macro into a regular module? That will never work. A worksheet_???? macro always goes into the worksheet where it is supposed to "work". Macros placed in regular modules have to be manually activated or called from other macros.

Since you also changed the currency selection to the Options page, the macro will have to be rewritten to reside in one sheet (Options) but make changes to another sheet when triggered. These are substantive changes.

The macro requires you watch a specific cell, that's why my posted macro watched cell [A1] as you had requested. When you moved it, you also edited it to take out that watch, so the macro has not target any longer specified. You can't do that.

If Target.Count = 1 Then

...this is meaningless for this macro, we need to be checking the target address. It now needs to be [B1] on the Options sheet.

Lastly, you moved the columns AND rows for this macro to change. These are truly important changes. You have to update your macros if you're not done fiddling with your sheet design.

Remove the other macro. Put this in the OPTIONS sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
    Set Rng = Sheets("Configuration").Range("I120:J147")
        Select Case Target
            Case "UK"
                Rng.NumberFormat = "[$£-809]#,##0.00;[Red]-[$£-809]#,##0.00"
            Case "DE", "FR", "NL"
                Rng.NumberFormat = "#,##0.00 [$€-1];[Red]-#,##0.00 [$€-1]"
            Case Else
                Rng.NumberFormat = "#,##0.00 ;[Red]-#,##0.00"
        End Select
End If
End Sub

It's working for me, though I want to be sure, you aren't trying to change the value in A1 on "Configuration" are you? I noticed you put the validation list back...that will confuse someone into thinking they CAN change it there. I took it out and put the =Options!B1 formula back in there.

It's simple...whatever sheet you are making the cell change in manually, that's the sheet that gets the worksheet_change macro customized for what's going on on THAT sheet. OK?

Works for me.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

shaunnehughes, please take a look at the forum rules and then start your own thread.

thanks.