Finding specific text and change the format

Dear forum members,

In my spreadsheet consisting on 443956 rows, I want to change the format of specific text as follows:

Find the text:
"1. Open a file per vendor an number according to the SAP vendor number.
2. For each vendor open files per financial year and month.
3. Use these files for supporting documents. These files does not replace the SAP process."

Change the font to italics dark grey

Each line of text on a separate row within the cell

Merge the text across cells in column C-O



Then

Find the following text just below (Fin Year):

"Replace the x in the number with the consecutive number."

Change the font to italics dark grey

Merge the text across cells in column D-O


Then

Then find the following text just below (Month)

"Replace the x in the number with the consecutive number."

Change the font to italics dark grey

Merge the text across cells in column E-O

The sheet consists of 443956 rows.


I am attaching an example of the workbook for your use and advice.


In anticipation.....

Louisa

You will need VBA to do this so consider re-posting on the Excel programming forum.

Thank you. Will do

Hi Louisa,

Take a look at the attached workbook and see if it does what you need.

It uses the following code:

Please Login or Register  to view this content.

I've taken the liberty of using the above routine to correct the typo's in Cells D2, D86 etc.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hallo Greg, I tried and it seems to be working. However the following dialogue keeps popping up. Is there a way to get rid of it?
Capture.PNG

Hi again,

Glad to hear that at least it seems to be working.

That message didn't appear when using the workbook you posted, but it's possible that your "real" workbook does contain merged cells. Before running the code it might be a good idea to check that cells which are going to be merged don't contain data, because otherwise data from all cells other than the first one in the merge range will be lost.

The way to prevent the message from appearing is to add the following highlighted statement to the existing code:

Please Login or Register  to view this content.

Strictly speaking, Alerts should be re-enabled when they are again required, but they will default to the enabled state when the routine finishes.


Hope this helps - please let me know how you get on.

I'll be away from tomorrow morning until next Tuesday evening - I should be able to reply at that stage.

Regards,

Greg M

Hallo Greg,

Yes you were spot on. I found that some of the cells had text in the cell next to it. I removed those redundant text and then the code worked like a charm.

Thank you.

Hi again Louisa,

Many thanks for your feedback and also for the Reputation increase

You're very welcome - glad I was able to help.

Best regards,

Greg M