Link to home
Start Free TrialLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

Help with CASE statement

Folks,
Admittedly, I am very POOR at CASE statements. In the example below here's my objective.

When the workbook change event is triggered I would like to evaluate which cell generated the change. In the view below cells O6:O9 a function is entered in each cell independently. I have cells Q6:R9 formatted as ";;;".  When a function, for example, in cell O6 has been enter I would like to change the formatting for range Q6:R6 set to "General". The same would apply for O7, O8 and O9 and their corresponding ranges Q7:R7, Q8:R8, and Q9:R9. Would a Case statement be the best way to handle this and what would be the code, please.
 User generated image
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

ActiveCell.Address will give you the cell address.
What happens if you format them as General before anything is entered?
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Frank Freese

ASKER

When the worksheet is activated range Q6:R9 is formatted as ";;;" I want to keep what's in that range but not visible to the user until a function has been entered.
I did mean worksheet, not workbook.
Target.Address  - I'm looking into that now
There's nothing wrong with that but what is the purpose?
My fault again - not enough detail.
Every time a function is entered O6:O9 the values in Q6:R9 (that contain formulas) changes.
If I don't ";;;" here's what they would see:
User generated imageand that's why I hide them when the worksheet is activated or the user elects to Try Again.
The number of cakes and kth percentile can be changed by the user when the Try Again. Every time they enter a function O6:O9 then the format would be "General" and the results would be different.
I'm not sure I understand. Why can't O6:O9 be blank and instead of having formulas there, do the calculation in the Change event.
OK - got it to work w/o Case but If instead using Target.Address that you suggested
thank you sir - I appreciate you pointing me in the right direction!