[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with CASE statement

Posted on 2014-08-09
10
Medium Priority
?
109 Views
Last Modified: 2014-08-09
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.
 Case statement
0
Comment
Question by:Frank Freese
  • 6
  • 4
10 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40251196
ActiveCell.Address will give you the cell address.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40251197
What happens if you format them as General before anything is entered?
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40251205
Sorry. You need to use Target.Address rather than ActiveCell.Address. And you meant Worksheet_Change, right?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Frank Freese
ID: 40251219
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
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40251236
There's nothing wrong with that but what is the purpose?
0
 

Author Comment

by:Frank Freese
ID: 40251265
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:
formulasand 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.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40251279
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.
0
 

Author Comment

by:Frank Freese
ID: 40251280
OK - got it to work w/o Case but If instead using Target.Address that you suggested
0
 

Author Closing Comment

by:Frank Freese
ID: 40251281
thank you sir - I appreciate you pointing me in the right direction!
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40251289
YW
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question