Frank Freese
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.
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.
ActiveCell.Address will give you the cell address.
What happens if you format them as General before anything is entered?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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?
ASKER
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:
and 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.
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:
and 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.
ASKER
OK - got it to work w/o Case but If instead using Target.Address that you suggested
ASKER
thank you sir - I appreciate you pointing me in the right direction!
YW