How do I dynamically change the Number Format of a cell

[johnw993]

One of my cells is used for reporting data. Depending on the choice made by the user (in other cells), this cell will show results that can either be percentages or numbers.
The problem I have is that the Number Format of the cell does not change to match the data it is getting.
The data the cell is receiving is formatted in their respective correct number format.

[Norie]

You might be able to use conditional fomatting based on the choice made by the user.

What choice are they making and how are they making?

[johnw993]

In several pull-down menus (validated cells) the user can select a name, a department and a date.
The report populates itself with the the type of activities for the department selected, and shows quantities for each activity (number of calls made, web visitors, closed sales, etc. etc) for each day, week, month and year (these numbers have been entered by employees using a UserForm).
At the bottom of the report are several rows doing calculations on the data above it (the quantity of activities). The same row will change dependent on the department selected. For one department it may say "Web Convert Rate" and shows a percentage result in all the date columns. For another department selected the row (and the same cell) will change and may say "Total Billable Hours" and returns a number in the date columns. The problem is that the format of the numbers does not change.