Formatting cells
Solved/Closed
Stace123
Posts
3
Registration date
Saturday July 25, 2015
Status
Member
Last seen
July 25, 2015
-
Jul 25, 2015 at 04:46 AM
Stace123 - Jul 28, 2015 at 08:41 PM
Stace123 - Jul 28, 2015 at 08:41 PM
Related:
- Formatting cells
- Clear only the formatting from the selected cell (leaving the content) - Guide
- How would you change all cells containing the word pass to green - Excel Forum
- Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
- Phone formatting software for pc - Download - File management
- Compare 2 text cells and show difference in the third cell ✓ - Excel Forum
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jul 28, 2015 at 11:44 AM
Jul 28, 2015 at 11:44 AM
Hi Stace,
Q1: Linking drop down lists.
1st step would be to create a list of products and there prices.
Create a named range (the field left of the formula bar that normally displays the cell currently selected) for each product.
Now in C4 we create a drop down list referring to the named range by using the formula: =INDIRECT(B4)
Q2: Using VBA to change number format.
After implementing the following code the format of C4 will change according to the country you chose in A1:
As you can see the format for Australia and Europe is the same as I couldn't find Australia in the list.
To use the format you want, record a macro > change the format > stop recording > paste the format from recorded macro to the above provided macro. This way you can add as many countries as you want.
According to your knowledge of Excel this can be quit a handful. The link below has a workbook which has all the above implemented to further assist you. It also contains a recorded macro (Module 1) so you can see how that would look like. The above code is located under Sheet1.
http://speedy.sh/DayTu/Stace123-formatting-cells.xlsm
Let us know if and where you get stuck to help you further.
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Q1: Linking drop down lists.
1st step would be to create a list of products and there prices.
Create a named range (the field left of the formula bar that normally displays the cell currently selected) for each product.
Now in C4 we create a drop down list referring to the named range by using the formula: =INDIRECT(B4)
Q2: Using VBA to change number format.
After implementing the following code the format of C4 will change according to the country you chose in A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Value = "USA" Then Range("C4").NumberFormat = "[$$-409]#,##0.00"
If Target.Value = "Australia" Then Range("C4").NumberFormat = "[$€-413] #,##0.00"
If Target.Value = "Europe" Then Range("C4").NumberFormat = "[$€-413] #,##0.00"
End Sub
As you can see the format for Australia and Europe is the same as I couldn't find Australia in the list.
To use the format you want, record a macro > change the format > stop recording > paste the format from recorded macro to the above provided macro. This way you can add as many countries as you want.
According to your knowledge of Excel this can be quit a handful. The link below has a workbook which has all the above implemented to further assist you. It also contains a recorded macro (Module 1) so you can see how that would look like. The above code is located under Sheet1.
http://speedy.sh/DayTu/Stace123-formatting-cells.xlsm
Let us know if and where you get stuck to help you further.
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Jul 28, 2015 at 08:41 PM
Cheers