# Thread: Format IF? (Excel 2000)

1. ## Format IF? (Excel 2000)

Is there a way to create a formula that will format the cell (e.g. Percent vs Currency vs General Number) based on specific conditions. For example, if the value of the cell is <1 then the result will be formatted as a %, but if it is >1, then it will use currency? If multiple formulas are needed this is fine.

Reason for request:
I have a map that has several text boxes corresponding to each particular state on the map. I have a combo box that selects from a list of categories that is used in a VLOOKUP to produce a number which is combined with the Indirect function, to reference a cell in a pivot table. However, some of these categories result in percentage values while others result in currency.

Thanks in advance for any assistance.

2. ## Re: Format IF? (Excel 2000)

You can use conditional formatting to change the format of a cell based on its value. Select "Format>Conditional Formatting..."

3. ## Re: Format IF? (Excel 2000)

Using this technique, I cannot change the format from percentage to Currency based on a particular value. This only allows you to change color, font, size, etc.

4. ## Re: Format IF? (Excel 2000)

You can use a custom format:
Format - cells - number (tab) custom:
[<1]0.00%;[>1]\$0.00;General

This will be 2 decimal percent when <1, currency with 2 decimal when >1 and general when =1

Steve

5. ## Re: Format IF? (Excel 2000)

The only way I know of to do this would be to use one of the worksheet event routines to change the format. Which event routine you would use would depend on exactly how the cell is being change. From the sould of it, you probably want the worksheet calculate event, but the worksheet change event is another possibility. If you need help doing this, could you upload an example workbook that shows what you want to do. That would save a lot of time and trial and error.

6. ## Re: Format IF? (Excel 2000)

Thank you Steve, this is what I was looking for. Will this custom format remain with the workbook or is there something else I need to do to ensure that when I distribute this, the custom formatting continues to work on other computers?

7. ## Re: Format IF? (Excel 2000)

Very true, my brain is not working today <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

8. ## Re: Format IF? (Excel 2000)

Custom formats are in the workbook and get transferred.

Steve

9. ## Re: Format IF? (Excel 2000)

Thanks again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•