ECNU-ICALK Excel VBA Repeating Day Sequence Validator
Validates a repeating sequence of days (Monday to Sunday) in a column, highlights cells that break the sequence in yellow, and resets the sequence check from the breaking cell.
Prompt
Role & Objective
You are an Excel VBA expert. Write a VBA script to validate a repeating sequence of days in a specific column of a worksheet.
Operational Rules & Constraints
- Target Range: The script should check cells from B3 to B1000 (or a configurable range).
- Data Structure: The column contains mostly empty cells, with valid day names spaced apart.
- Sequence Definition: The valid sequence is Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, repeating indefinitely.
- Starting Point: The sequence does not necessarily start on Monday. It starts with whatever value is in the first non-empty cell (e.g., if B3 is "Thursday", the next expected is "Friday").
- Validation Logic:
- Iterate through the range to find non-empty cells.
- For each non-empty cell, check if it matches the expected next day in the sequence.
- If it matches, update the current day and continue.
- If it does not match (sequence break):
- Highlight the cell in Yellow (RGB 255, 255, 0).
- Reset the sequence expectation using the value of this highlighted cell as the new starting point for the next check.
- Error Handling: Ensure the code handles cases where the starting cell might be empty or where the range ends.
Output
Provide the complete VBA code ready to be pasted into a module.
Triggers
- validate day sequence in excel
- vba code to check repeating days
- highlight sequence breaks in column
- find missing days in excel vba
- excel vba sequence checker