Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating of cells
Good afternoon,
I have an array of numbers in excel. I want then to display as percentage if I've selected "proportional" from a drop down menu. I want them to display as a number (0.00 for example) if I select "absolute" these are the only two options in the menu. I'ved tried using the Format code help in Excel but I can't manuipulat it to get it to work how I want. Any help will be greatly apprecaited. Thank you! Michelle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating of cells
Let's put the drop down in column A and the data to be formatted in column B.
Enter the following in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Dim A1 As Range, B As Range Set A1 = Range("A1") Set B = Range("B:B") If Intersect(Target, A1) Is Nothing Then Exit Sub Application.EnableEvents = False If A1.Value = "proportional" Then B.NumberFormat = "0.00%" Else B.NumberFormat = "General" End If Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu201002 "michelle439731" wrote: Good afternoon, I have an array of numbers in excel. I want then to display as percentage if I've selected "proportional" from a drop down menu. I want them to display as a number (0.00 for example) if I select "absolute" these are the only two options in the menu. I'ved tried using the Format code help in Excel but I can't manuipulat it to get it to work how I want. Any help will be greatly apprecaited. Thank you! Michelle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formating of cells
Awesome, thank you very much, that works perfectly.
And thanks for the links, I'll check them out. Cheers, Michelle "Gary''s Student" wrote: Let's put the drop down in column A and the data to be formatted in column B. Enter the following in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Dim A1 As Range, B As Range Set A1 = Range("A1") Set B = Range("B:B") If Intersect(Target, A1) Is Nothing Then Exit Sub Application.EnableEvents = False If A1.Value = "proportional" Then B.NumberFormat = "0.00%" Else B.NumberFormat = "General" End If Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu201002 "michelle439731" wrote: Good afternoon, I have an array of numbers in excel. I want then to display as percentage if I've selected "proportional" from a drop down menu. I want them to display as a number (0.00 for example) if I select "absolute" these are the only two options in the menu. I'ved tried using the Format code help in Excel but I can't manuipulat it to get it to work how I want. Any help will be greatly apprecaited. Thank you! Michelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating on Several Cells | Excel Discussion (Misc queries) | |||
Conditional Formating in Blank Cells | Excel Worksheet Functions | |||
conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
Conditional Formating for Formula Cells | Excel Discussion (Misc queries) | |||
HELP ME PLEASE!! CONDITIONAL FORMATING HIGHLIGHTING CELLS. | Excel Discussion (Misc queries) |