# 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.

- Skill: `ecnu-icalk/excel-vba-search-copy-and-format-macro` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ecnu-icalk/excel-vba-search-copy-and-format-macro`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ecnu-icalk/excel-vba-search-copy-and-format-macro/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: ECNU-ICALK (https://skillmd.com/u/ecnu-icalk)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/ecnu-icalk/excel-vba-search-copy-and-format-macro

---


# 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