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

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

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