|re: " I am unsure how this helps because if the copy function is invoked it should be able to paste with formatting right?"|
It helps because we were guessing at what you were copying, how it was formatted, etc.
For example, I had no clue that part of the test was formatted differently than other parts, that the cell had multiple lines, etc. I've been doing this for a long time, so please trust me when I say that the more details that we have, the easier it is for us to test our suggestions. Now that I know exactly what you are working with, I can test my code against a real live piece of your data. If I get the same error, then I know that the suggested code doesn't work with your data. If I can't duplicate the error, that we need to start looking elsewhere.
That said, I am having no problem with the code I posted when tested against various versions of your example. By "versions" I mean that I made 5 copies of your example, changed one or 2 letters so that each entry was unique, and then formatted each version differently.
I changed font colors of some of the words in a given cell, I filled different cells with different colors, I doubled underlined or italicized or bolded different words in different cells. When a value was chosen from the drop down, all formatting was copied exactly as shown in the source list.
I then tried to duplicate your error and was not able to do it. I did notice that the workbook I set up had the Data Validation Source List in Sheet 2 and the drop down in Sheet1 - which is the reverse of what you described, but I don't think that that would cause an error on the line you mentioned. I then set up a workbook as you described and did not get any error. I did have to change the following instruction from this:
Frankly, I can't find a way to get the code to fail at
Set c = .Find(Target, lookat:=xlWhole)
I don't think it's because "the text is too long" unless there is something different about the other items in your list. The example you posted certainly didn't cause a failure, even after some fairly intensive formatting options. There must something else going on that we don't about.
BTW...there is one more thing that you should be aware of:
If the code makes it past the Application.EnableEvents = False instruction and fails before the Application.EnableEvents = True instruction, the Worksheet_Change macro will not run again until EnableEvents is set back to True.
That can be done with this short piece of code:
Application.EnableEvents = True
Since EnableEvents is an Application level instruction, it's remains set at its last state until specifically told to change. Simply "quitting" a failed macro will not turn it back on.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03