Advanced VBA macro tools


New Member
Mar 30, 2015
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.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...