Merge Cells When Other Merged Cells Receive Specific Data

First of all, I need to say that I'm aware of how much everyone here hates merged cells. I've henceforth tried to minimize my usage thereof, but am still using some due to necessity. Now that that's out of the way, here's my question.

I'm looking for a code that will merge cells J9 through X9 and display the text 'SEE PAGE TWO FOR MORE DETAILS' when merged Cell B9:H9 receives the value of L* (meaning only the letter 'L' plus anything after it).

Is there a solution to this, or will all those merges throw everything into utter chaos as usual?

Use this

Please Login or Register  to view this content.

Hmm, I can't seem to get that working. The code is meant to be pasted in the sheet code, is it not?

All you need was to add the sub statement. I thought you r gong to use it within an existing code.
Use this:

Please Login or Register  to view this content.

Well, yes I was... but it was giving me errors. When I try to use it alone, it doesn't want to do anything.. maybe I should upload my sample.

The purple cells are the ones that will receive the L* info, the blue ones in the same row are the ones that need to be merged should that happen to the purple.

there are some issue with your code.
Which cell are you intending to double click?

Yeah, I haven't been able to get that part working yet. I9:I78, I90:I159, and I171:I240 are the ones that need the double click.

try this instead of the one you have in your code

Please Login or Register  to view this content.

Pal, you're amazing. You got the double click thing working beautifully! I thought I'd have to ask for help with that separately. Thank you so much!

As for the merging thing - I'm sorry, but I can't get it to work. I've attached the worksheet as it is at this point, along with some sample merged cells that I'm looking for, in case we're not understanding each other fully on that point.

the merging is working as well in the sheet you lastly attached. for exmple, double click on the cell I15

Oh! I see what you did! Ah.. I'm sorry.. the blue cells are supposed to merge like that when the purple cells preceding them receive an L2 or an L404 or something like that. The double click thing is a separate function altogether. I'm so sorry.. that is very cool, though!

oK,
Delete all the existing codes in the code page and paste the below one.

Please Login or Register  to view this content.

Wow...

That is pretty sweet. It is just about perfect, only right now if I enter any data that starts with the letter 'L', such as LENGTH, your awesome code kicks in and merges those cells. Is there a way to avoid the code from merging if a word is entered? Or maybe specify that only the letter 'L' followed by a number or numbers should trigger it?

Are we also able to have the code force the 'SEE PAGE TWO' message to be centered in the cells vertically? The rest of the cells need to be formatted the way they currently are, on Bottom Align.

And again, wow, thank you.

Here you go:

Please Login or Register  to view this content.

Man you're fast. That is more like it! The only 'bug' I can still find is when I have an 'L'-word in a purple cell, and double click the one after it to get the checkmark. The blue cells suddenly merge for some reason. Other than that, I think it's perfect!

Oh, and I meant to say vertical alignment before, but I was able to change that in the code myself.

Sorry it was a mistake that I made.
Please use the following.
I will not have any access to computers until tomorrow in case you need anything

Please Login or Register  to view this content.

No need to apologize! That was amazing. It works beautifully! Thank you so, so much!

Another quick question! Would it be possible to have any blue merged cells with the message in it revert back to the original format if the data in the purple cell before it is deleted or changed to a value without the 'L'?

Try this

Please Login or Register  to view this content.

Very cool! That works quite well, unless I change a value of say L1 to LENGTH, then the respective blue cells don't un-merge. Also, if I have say three L-values in a vertical row on the purple cells, like L2, L3, L4, and I select them all, right-click and choose 'Clear Contents', only the blue cells for L2 get un-merged. That last one's not that big a deal, though, I don't want to get over the top fussy here. I am very impressed with your work. Thank you!

That one was relatively hard to do

Please Login or Register  to view this content.

That works incredibly well! I am extremely grateful to you for sharing your expertise. Thanks a million!

Ah, I found a rather bad bug. I'm sorry.. you've done incredibly much already. The blue cells, when they aren't merged, receive user-inputted data.. my current issue is that when there's data in them, and I enter data into their respective purple cells - data that isn't an 'L'-value; whatever information is currently in the blue cells (Column JKL, the others are fine) gets erased. I hope this isn't too difficult to fix... and I apologize again..

Fixed:

Please Login or Register  to view this content.

Thank you yet again, Kelshaer, this fixes the issue I mentioned - but now removing an 'L'-value puts our 'See Page Two' message into the first blue cell in Column JKL..

Here you go:

Please Login or Register  to view this content.

Kelshaer, that is perfect. Many thanks once again!

I have another quick question! I'm asking it here since it's very much related to this thread:

If one of the cells in Column A to the left of the purple cells receives a dash (-) in it somewhere, the purple merged cell to the right of it needs to receive the message "SEE REPORT". When or if the dash is removed, the message needs to disappear.

Would this be difficult to incorporate yet?

Use this:

Please Login or Register  to view this content.

This seems to work well if the cell in Column A has only a dash and nothing else; there will always be something else with the dash, though, like 'A-G' or something similar to that. Is there a way to make the code search for a dash in the cell if there are other characters in there with it? I apologize for not being clearer.

Please Login or Register  to view this content.

This is lovely. Thank you so much! Have a great week. You've made mine much easier.

What would I have to add to the code to have it checkmark the corresponding box in the I Column when the "SEE REPORT" message is added, and have it un-check when the message is removed? I don't think it should be linked to the dash recognition code, as that might mess things up.. what are your thoughts, though?

P.S. I promise I'm almost done.

Please Login or Register  to view this content.

This works! And it's amazing! Thank you yet again, kelshaer.

I came across a situation today where I needed to add a value of 'Lą.001' to one of the purple cells, and the adjacent blue cells needed that 'SEE PAGE TWO' message; however, it didn't do our merging thing. How could we update the specific recognition in our code?

These are the cases when it should do the merge:

L
L1
L202
Lą.003

I can't think of any other cases.. these are the ones it shouldn't do the merge:

LENGTH

I can't think of any more there either.

Could the code maybe only do the merging thing if the L is followed by anything but another alphabet? That might do it... What do you think?

Here you Go

Please Login or Register  to view this content.

Hmm, that's still not working.. what did you change? It seems to work exactly the same as before.

Little changes,
Changes Highlighted in red:

Please Login or Register  to view this content.

Thank you! It works wonderfully now!