Change cells number format off an other cells value

[MDuff]

Hi,

I am using an if statement that depending on the value in B1 is a 1 or a 2 will ether return a number or a Percentage. I am trying to get excel to change the format if the value in b2 is 1 to a % format and if it is 2 to a number format.

I have tried using these two rules in conditional formatting but it is not working and getting "stuck" in one of the formats

=$B$1=2 format as Number
=$B$1=1 Format as Percentage


Here is an example that should be formatted as a percentage

Excel 2007
	A	B	C	D
1		1	% Used
2			Net Seats
3			28-Oct-13	4-Nov-13
4	Seat Utilization% for Actual Prod Staff		1	1
Summray

Cell Formulas
Range	Formula
C3	=Springfield!B8
C4	=IF($B$1=1,VLOOKUP(C$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(C$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(C$3,Greeley!$B$8:$BI$33,9,FALSE)))
D3	=C3+7
D4	=IF($B$1=1,VLOOKUP(D$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(D$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(D$3,Greeley!$B$8:$BI$33,9,FALSE)))

Here is an example as a number

Excel 2007
	A	B	C	D
1		2	% Used
2			Net Seats
3			28-Oct-13	4-Nov-13
4	Seat Utilization% for Actual Prod Staff		91	96
Summray

Cell Formulas
Range	Formula
C3	=Springfield!B8
C4	=IF($B$1=1,VLOOKUP(C$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(C$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(C$3,Greeley!$B$8:$BI$33,9,FALSE)))
D3	=C3+7
D4	=IF($B$1=1,VLOOKUP(D$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(D$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(D$3,Greeley!$B$8:$BI$33,9,FALSE)))

Any Ideas I am willing to use VBA I was thinking of a change event but I could not figure out how to get the code to work

 

[RoryA]

You could assign a macro to the combo box that just sets the numberformat of the cell(s) to either "0" or "0%" depending on selection but it would seem easier to just type 1 or 2 manually to me.

 

[MDuff]

You could assign a macro to the combo box that just sets the numberformat of the cell(s) to either "0" or "0%" depending on selection but it would seem easier to just type 1 or 2 manually to me.

thanks and I was the final user of the sheet I just would do that unfortunately it will be quite a few that use it and I was really hoping to avoid them typing.


thanks once more for all your help!!!!

 

[RoryA]

Basic macro would be:

Sub DropDown1_Change()
   Dim strFormat As String
   If ActiveSheet.DropDowns(Application.Caller).Value = 1 Then
      strFormat = "0"
   Else
      strFormat = "0.0%"
   End If
   Range("A1:A10").NumberFormat = strFormat
End Sub

BTW, please read the forum rules on cross-posting (they are much the same in almost all internet forums) and provide links to your cross-posts in future. Thanks.