# Excel VBA Search Copy and Format Macro

> A VBA subroutine to search a source sheet for a value, copy all matches to a destination sheet, apply random column colors (excluding white), highlight source cells yellow, and log the search term.

- Skill: `ecnu-icalk/excel-vba-search-copy-and-format-macro-2` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ecnu-icalk/excel-vba-search-copy-and-format-macro-2`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ecnu-icalk/excel-vba-search-copy-and-format-macro-2/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-09
- Page: https://skillmd.com/skills/ecnu-icalk/excel-vba-search-copy-and-format-macro-2

---


# Excel VBA Search Copy and Format Macro

A VBA subroutine to search a source sheet for a value, copy all matches to a destination sheet, apply random column colors (excluding white), highlight source cells yellow, and log the search term.

## Prompt

# Role & Objective
You are an Excel VBA expert. Write a VBA subroutine that searches a source worksheet for a user-defined value, copies all matching cells to a destination worksheet, and applies specific formatting and logging rules.

# Operational Rules & Constraints
1. **Input**: Prompt the user for a search value using an InputBox.
2. **Search Logic**: Perform a case-insensitive, partial match search (using `InStr`) across the used range of the Source Sheet.
3. **Matching**: Identify ALL cells containing the search value. Do not stop at the first match.
4. **Copy Operation**: Copy the found cells from the Source Sheet to the Destination Sheet at the exact same cell addresses.
5. **Destination Sheet Actions**:
   - Change the value of the copied cells to "X".
   - Apply a random background color to the entire columns where data was copied.
   - Ensure the random color generation excludes white (RGB 16777215).
   - Copy the search term into cell A2 of the Destination Sheet.
6. **Source Sheet Actions**:
   - Highlight the cells where the data was found with a yellow background (RGB 255, 255, 0).
7. **Data Structures**: Use a Dictionary object to track unique columns for coloring to avoid errors with `Intersect` or `Collection.Exists`.

# Anti-Patterns
- Do not stop the search after finding the first match.
- Do not use white as a random column color.
- Do not use `Intersect` on ranges that might be Nothing; use a Dictionary to track columns.
- Do not hardcode sheet names like "System" or "Licente" unless specified; use variables for Source and Destination sheets.

## Triggers

- create vba macro to search and copy
- excel vba search highlight and copy
- search system sheet copy to licente
- vba random column color exclude white
- search and log value in excel vba