Clean up IF statement with multiple cell references

[inq80]

Hello,

Anyone know of a tidier way of writing the below? It's some code I've got on a sheet module of a workbook which is being used to trigger a macro dependent on which cell is clicked. There's 12 in all for the months and im sure there's a shorter way of writing each month out without a new if statement each time.

Please Login or Register  to view this content.

I'm look for something along the lines of "if Jan (j7) clicked; change it's colour to XXXX and cells N7 + R7 + V7 + Z7 colour to XXXX etc...

I feel else if would be good but cannot get it to work.

Thanks

[inq80]

edit:

got the EsleIf statement working.

So just curious now if anyone knows a better way of writing:

Please Login or Register  to view this content.

[TMS]

You could do something like this for the January cell:

Please Login or Register  to view this content.

Regards, TMS

[MarvinP]

Hi inq,

See if this will work for you. This is the code for J7. I think you need a little For..Next loop with a Step of 4.

Please Login or Register  to view this content.

[inq80]

thanks guys.

very interesting solutions i never even considered. I'm off to look at the other all code again to review.

I'm basically setting up some buttons on screen without actually using buttons.

The colour change is just to see which month has been selected, then load the appropriate data accordingly. I like how your code works MarvinP but cannot see how this would change them into selectable buttons; each one once clicked would need to change to the purple colour whilst the last one changes back to grey.

a bit more playing around for me i think.

thanks again guys though.

[MarvinP]

Hi inq80,

See the attached for another possible answer. If you double click on any cell in the first 20 rows (read the code and you can change this) it will clear all background colors and highlight the ones I think you want. Here is the workbook so you can see and read the code. Now that you've explained a bit more, I think this is what you really want.

[inq80]

Hi inq80,

See the attached for another possible answer. If you double click on any cell in the first 20 rows (read the code and you can change this) it will clear all background colors and highlight the ones I think you want. Here is the workbook so you can see and read the code. Now that you've explained a bit more, I think this is what you really want.

Thanks MarvinP

It's good but not quite what I'm after. I've re-done sheet2 with what i have so far so you can see what i needed. I just need a better way of writing the line that changes all the non-selected cells back to grey; seems messy at the moment.

Copy of Event Double Click to show cells to be added.xlsm

[MarvinP]

OK here is another try.

Double click on D5 on sheet2 and see what happens.

[inq80]

that bounces around all over the place no matter which cell you click.

[TMS]

Did my solution not do what you were asking for? When you select a cell, it changes the colours as described. Easy enough to replicate for other months.

[sktneer]

What you have on Sheet2 module right now, can be written like below also using the Select Case statements.

Please Login or Register  to view this content.

[sktneer]

@TMS

He has merged cells so If Target.Cells.Count > 1 Then Exit Sub will not allow the code to proceed further.

[sktneer]

Or even simply this, which seems very much identical...
The previous code will produce an error if you select a single cell i.e. unmerged cell.

Please Login or Register  to view this content.

[sktneer]

OR simply this.......

Please Login or Register  to view this content.

[jindon]

try

Please Login or Register  to view this content.

Oh, I didn't see sktneer's.

[TMS]

@sktneer: I did not know that when I asked the question. BUT ... you do need something or, if you select a whole row, or multiple cells, the effect is "interesting".

I would suggest:

Please Login or Register  to view this content.

Regards, TMS

[inq80]

OR simply this.......

Please Login or Register  to view this content.

That's beautiful!

Far more elegant than mine

I couldn't get the range thing to work for only selected cells in a row so ended up with my own code as posted. Of course it's far easier just to turn them all grey; then turn the target cell purple. Thanks sktneer.

also thanks to everyone else for replying.

[TMS]

Note post #16.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[sktneer]

You're welcome. Glad it worked for you.

Don't forget to include a line of code what TMS has suggested i.e.

Please Login or Register  to view this content.

Otherwise you will get unexpected result, so it would be better to exit sub if more than 3 cells are selected at once.

[jindon]

Multiple cells could be selected, so should like this

Please Login or Register  to view this content.