Hello,
How about conditional formatting?
First, I would add a new, hidden column to display the count of unique values "so far" going down the list.
So, if I put the data in B1, then in A1 I would have:
Code:
=ROUND(SUMPRODUCT(1/COUNTIF($B$1:$B1,$B$1:$B1)),0)
Then copy down to the end.
The numbers will increase with every new unique value it finds in the list as the formula is copied downward.
You can then create a conditional format formula based on this info.
- Select B1:E44
- Conditional Formatting -> New Rule...
- Use a formula to determine which cells to format
- Use
- Apply a light blue
- OK/Apply
- Add new rule
- Use a formula to determine...
- Use
- Apply a darker blue
- Hide column A
If you can live without the borders, it will work pretty well for you and you won't have to run a macro every time. I hope this helps.
Attached is an example macro auto format.xlsx
Bookmarks