Conditional Formatting or VBA question
Posted by Adam S. on December 12, 2001 1:18 PM
Hey all,
Using validation, I have single input cell where you can only have the word "percentage" or "count".
My output cells have a basic if then statement that performs one of two calculations depending on the current value of the input cell.
As you may have already guessed, when the input cell is set to "percentage" the output cells use a formula that kicks out a percentage, when the input cell has it's value set to "count" the output cells kick out an integer value.
My problem involves the number formatting. If possible, I'd like to have the output cells automatically formatted as a percentage (with one decimal place) when the input cell is set to "percentage". When the input cell is set to "count", I'd like the format to be automatically set to a standard number format (zero decimal places).
I attempted conditional formatting, but the "number" tab where you'd normally see under format\cells were curiously missing.
I was able to use a basic macro that simply formats the output range depending on the Input cell's current value, but I do not know the code for having the macro run everytime the Input cell is changed (assuming there is one). So at this point I have to manually run the macro to force the format change.
My question: Is there a way to automatically format these output cells (by percentage/number/decimal places) with a change in an input cell?
Any ideas would be helpful. Thanks!
Adam S.
Posted by bob Umlas on December 12, 2001 1:34 PM
How about something like this (keep cell formatted as general):
Assume your current calculation is simply =a5/e2, and the cell with the validation is A2:
Use this:
=TEXT(A5/E2,IF(A2="percentage","0.0%","#,##0.00"))
That should work....
HTH
Bob Umlas
Excel MVP
Posted by Gary on December 12, 2001 1:37 PM
I'm not sure you need VBA at all. You can control the formatting of your output using the TEXT() function in Excel proper.
You could add the formatting you need to your IF formula.
E.g. =TEXT(0.05,"0.0%") will give you 5.0%,
+TEXT(10,"0") gives 10.
Look at Excel help for more info
Posted by Dan on December 12, 2001 1:38 PM
Assuming the input cell is A1.
Cut out the body of the macro that you created to format the cells and put it in the middle of this one and place the whole thing on the sheet that you want it to run on. Write back if you need more clarification or if it isn't working.
Private Sub Worksheet_Change(ByVal Sh As Object, ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
<<PLACE YOUR CODE HERE>>
End If
End Sub
What this does is run the macro when there is any change in cell A1. Good luck
Posted by -the VBA insert was also informative - Adam S. (NT) on December 12, 2001 1:46 PM
Thanks All! (I totally forgot about TEXT)
nt
Posted by Dan on December 12, 2001 1:48 PM
Me too! :)
