Advanced VBA macro tools

[Marrone]

Hi everyone,

I am a translator dealing on daily basis with several excel files which share almost the same structure and I am trying to come up with a series of advanced macros that can automatize multiple and redundant tasks I have to perform in each of them.

Unfortunately, my knowledge of VBA coding is very limited, so I apologize in advance for any misunderstanding. The idea is to come up with a Userform including multiple tabs, each with its own macro. Here's the list of the tasks I would like to include:

• Advanced Search

Method #1: Search a specific term

Enter a term to search either in column "ENGLISH" or "TRANSLATION" (give the option to select which one), Ideally, include check-box options to match the case or the entire cell content and specify if the search is required for only the selected sheet or all of them. Then generate a report sheet with the following structure:​

​

1. 
   [*=1]Column A: List the hyperlinks of each cell found in the selected column.
   [*=1]Column B: List all cells found in the selected column (ideally with the searched word highlighted in red).
   [*=1]Column C: List all the respective cells in the other column.
   [*=1]Include a button to paste any changes applied to the column "TRANSLATION" back to the respective cell.

Method #2: Search specific colors

It's similar to Option 1, but it searches for only cells highlighted with specific colors (ideally using check-box options to pick which colors to search) in either column "ENGLISH" or "TRANSLATION", no matter their content. The report sheet should be exactly the same as Option 1.​

• Issue lookup

Check all tabs and look for any of the following issues:

1. Inconsistent numbers, line-breaks, punctuation or capitalization between the cells in column "ENGLISH" and "TRANSLATION".
2. Double spaces, unsupported characters, repeated words, cells with 255 characters or trailing spaces in "TRANSLATION".
3. Include an option to check only GREEN cells in "TRANSLATION".

Then generate the same worksheet as Advanced Search, including an additional column stating the specific issue.
​

​

• Large cells

Select one or more specific cells in the column "ENGLISH", set the column "TRANSLATION" as reference, then set a line-break token (e.g. Alt+Enter,
, etc.), then generate a report sheet with the following structure:

​

1. 
   [*=1]Column 1: Insert the cell ID.
   [*=1]Column 2: Insert the ENGLISH cell text and insert an empty row for each specified token found.
   [*=1]Column 3: Same as Column 2 for "TRANSLATION".
   [*=1]Column 4: Check if cells in each row match. If yes, highlight the cell in GREEN; if not, highlight the cell in RED.
   [*=1]Insert a button to refresh the comparison.
   [*=1]Insert a button to apply all changes to the "TRANSLATION" column back to the relevant cell, replacing all empty rows with the specified token.

​

• Highlight tabs

Check all the sheets (except the ones highlighted in BLACK) and, if the column "TRANSLATION" has any cell highlighted in GREEN, then highlight in GREEN the relevant tab. Similarly, if there's no GREEN cell in "TRANSLATION", remove any color the tab is highlighted with.
​

I understand these tasks require a significant amount of coding, but I would like to know if you guys think it's something that can be achieved.

Thanks in advance for your help.

 

[Marrone]

Anyone?