# Excel: Change cells number format off an other cells value

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

ABCD
1 1 % Used
2 Net Seats
328-Oct-134-Nov-13
4Seat Utilization% for Actual Prod Staff11

Summray

Worksheet Formulas
CellFormula
C3=Springfield!B8
D3=C3+7
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)))
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
ABCD
1 2 % Used
2 Net Seats
328-Oct-134-Nov-13
4Seat Utilization% for Actual Prod Staff9196

Summray

Worksheet Formulas
CellFormula
C3=Springfield!B8
D3=C3+7
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)))
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 :(