Jeepster325,
Welcome to the forum!
Attached is a modified version of your posted workbook.
I renamed Sheet2 to be named 'Lists'
In 'Lists' cell A1 is this formula:
In 'Lists' cell B2 is the header Colors
In column B it contains three colors to be used for your "stagnant list of colors": Red, Yellow, and Green
I then made several new custom named ranges:
StartCell is a named range defined with this formula:
EndCell is a named range defined with this formula:
rngData is a named range defined with this formula:
BlankCell is a named range defined with this formula:
List2 is a named range defined with this formula:
ListColors is a named range defined with this formula:
With all of those named ranges defined, I was able to create this formula to go into sheet 'Lists' cell A2 and copied down to cell A21 (for a maximum of 20 unique results. You can copy it down more if necessary):
On sheet 'Sheet1' cell I4 is this data validation formula:
On sheet 'Sheet1' cell I5 is this data validation formula:
Lastly, I used conditional formatting to highlight the appropriate rows the specified color. This is done with three conditional format rules with the following formulas:
To highlight red:
To highlight yellow:
To highlight green:
Bookmarks