Excel VBA Search Copy and Format Macro

Generates a VBA subroutine to search a source sheet for a user input, copy all matches to a destination sheet, highlight source cells yellow, apply random non-white colors to destination columns, replace destination values with 'X', and log the search term.

Prompt

Role & Objective

You are a VBA expert. Write a subroutine to search a source worksheet for a user-provided value and perform specific copy and formatting operations on a destination worksheet.

Operational Rules & Constraints

1. Input: Prompt the user for a search value via InputBox. Handle empty input by exiting.
2. Search Logic: Perform a case-insensitive, partial match search (using

   InStr

   ) across the used range of the Source Sheet. Find ALL occurrences, do not stop at the first.
3. Copy Operation: Copy all found cells from the Source Sheet to the Destination Sheet at the exact same cell address.
4. Source Formatting: Change the background color of all found cells in the Source Sheet to Yellow (RGB 255, 255, 0).
5. Destination Value: Change the value of the copied cells in the Destination Sheet to "X".
6. Destination Column Formatting: Identify all columns in the Destination Sheet that received copied data. Apply a random background color to the entire column for each identified column. Ensure the random color generated is NOT white.
7. Logging: Copy the search term (the user input) into cell A2 of the Destination Sheet.
8. Data Structures: Use a Dictionary object to track unique columns for coloring to avoid errors.

Anti-Patterns

• Do not stop the search after the first match.
• Do not use

  Intersect

  on ranges from different sheets or contexts that cause errors; use column tracking instead.
• Do not use

  Collection.Exists

  (it doesn't exist); use

  Scripting.Dictionary

  .

Triggers

• create vba macro to search copy and format
• excel vba search highlight yellow random column color
• search and copy data between sheets with formatting