CONDITIONAL FORMATTING
Posted by Dana on July 24, 2001 4:38 PM
I am having a problem with conditionaly formatting in Excel 2000. I am using an offset formula to bring the data into a cell. This cell will change as I change a pulldown menu. For example, sometimes it will contain a percentage, sometimes a currency value, sometimes a number. I want to graph this cell value, so I need it to be conditionally formatted. The people who see this sheet will not want to go into every cell and format the cells manually as they change the pulldown.
I know how to use conditional formatting to format color, border, font, etc....but I need to change the NUMBER format. Does ANYone know?
Posted by Lieuwer on July 26, 2001 12:17 AM
One solution, but I agree it is not very elegant is to have an other cell in the same sheet contain the formatting condition.
For exsample a percentage is entered and as such recognised. This same action triggers the cell containing the formatting condition to change in "Percentage". In the cell you want to format the formula becomes ="Percentage" and the formatting condition.
As said not elegant but it works. With Conditional formatting you have only three options. You can write a macro to format your cell and then you have many more options.
success
lieuwer
Posted by Ian on July 26, 2001 5:29 AM
lieuwer ......"You can write a macro to format your cell and then you have many more options." HOW?
It's easy to say but hard to do, I've had 4 ideas put forward for this mysterious answer. BUT none have helped me yet.
phew, I've calm down now.
The answer I've got all do just that, conditionally format the cells more than 3 times (ONLY IF YOU ACTUALLY ENTER DATA IN TO THE CELLS DIRECT).
What I need is something that changes the format when a cell is updated via a link???
this seems to be the impossible dream.
Ian
Posted by Ivan F Moala on July 27, 2001 8:20 PM
Re: lieuwer ......"You can write a macro to format your cell and then you have many more options." HOW?
To do this you will need to use the Worksheet calculate event eg.
Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = Range("A1")
Select Case Target
Case 1
Target.Interior.ColorIndex = 1
Case 2
Target.Interior.ColorIndex = 2
Case 3
Target.Interior.ColorIndex = 3
End Select
End Sub
Ivan
