Hi NessieMe,
What about this? If you're list isn't too long, you could nest it in a CHOOSE() function, then pick the correct position uisng the MOD() function. Based on your example, tossing this in C4, C6, etc (beside your existing items) does work:
=CHOOSE(IF(MOD(COUNTA(B$4:B4),4)=0,4,MOD(COUNTA(B$4:B4),4)),"Red","Green","Blue","Orange")
The key here is that we've provided it four items. If you went to five, you'd need to modify it to:
=CHOOSE(IF(MOD(COUNTA(B$4:B4),5)=0,4,MOD(COUNTA(B$4:B4),5)),"Red","Green","Blue","Orange","Purple")
Hope this helps,
Bookmarks