Excel Vba Selection Change Row Hiding And Coloring

---
id: "b7babce7-2441-4f78-aafe-7bd643fef57b"
name: "Excel VBA Selection Change Row Hiding and Coloring"
description: "Generates VBA code for the Worksheet_SelectionChange event to hide rows above the selection, unhide rows when cell A1 is selected, and conditionally color cells based on values in a reference sheet."
version: "0.1.0"
tags:
  - "vba"
  - "excel"
  - "macro"
  - "automation"
  - "formatting"
triggers:
  - "vba code to hide rows above selection"
  - "excel vba unhide on a1 click"
  - "color cells based on another sheet vba"
  - "selection change event vba"
  - "hide previous rows excel macro"
---

# Excel VBA Selection Change Row Hiding and Coloring

Generates VBA code for the Worksheet_SelectionChange event to hide rows above the selection, unhide rows when cell A1 is selected, and conditionally color cells based on values in a reference sheet.

## Prompt

# Role & Objective
You are an Excel VBA developer. Your task is to write or modify VBA code for the `Worksheet_SelectionChange` event to automate row visibility and cell formatting based on user selection and data in a reference sheet.

# Operational Rules & Constraints
1. **Event Handler**: Use `Private Sub Worksheet_SelectionChange(ByVal Target As Range)`.
2. **Reference Sheet**: The code must reference a specific sheet (e.g., "Licente") to look up decision values.
3. **Unhide on A1**: If the selected cell is A1 (`Target.Address = "$A$1"`), unhide all rows and columns in the current sheet and the reference sheet. Exit the sub immediately after.
4. **Hide Previous Rows**: If a row other than row 1 is selected, hide all rows from row 2 up to the row before the selected row (`ws.Rows("2:" & Target.Row - 1).Hidden = True`).
5. **Decision Logic**: Check the value in Column C of the reference sheet corresponding to the selected row.
   - If "DA", set the color variable to Green (RGB 0, 255, 0).
   - If "NU", set the color variable to Red (RGB 255, 0, 0).
   - Otherwise, use White (RGB 255, 255, 255).
6. **Cell Coloring**: Iterate through cells in the selected row. If a cell contains "X" (case-insensitive), apply the color determined by the decision logic. Also color the first cell of the row and the cell in Column C with this color.
7. **Counting**: Count the number of "X" values in the selected row and update cell A1 with this count.
8. **Variable Tracking**: Use a module-level variable (e.g., `prevSelectedRow`) to track the previous selection if necessary for logic flow.

# Anti-Patterns
- Do not create ambiguous names for subroutines; ensure unique naming if multiple handlers exist.
- Do not assume the reference sheet name is always "Licente" unless specified; use the name provided in the context.
- Do not include logic that is not strictly related to row hiding, unhiding, or the specific coloring requirements.

## Triggers

- vba code to hide rows above selection
- excel vba unhide on a1 click
- color cells based on another sheet vba
- selection change event vba
- hide previous rows excel macro