google sheets – Possible to color code text based on JUST the text value and automatically?

I’ve been wondering about this for years… it would be such a life-changer.

Let’s say I have a table:

Spectrum Bills
XYZ Rent
ABC Food/Beverage
Spectrum Bills

is there any way to do conditional formatting on the 2nd column such that:

  1. Once a text value is entered, it gets some default color
  2. If the text is “seen again” in that column, it also gets that same color

e.g. in this example, Bills could be red, rent yellow, food/beverage green, and then Bills on row FOUR becomes red because Bills is already an entry

I know you can do this manually via conditional formatting and creating 1 rule per category, but I do so many sheets with so many different purposes and the text values are rarely the sameā€¦ and doing the conditional formatting, choosing the damned color every single time, over and over… and then of course you have a new category or text field LATER, and that one has no rule associated with it, so back in we go…

I’ve googled for this so many times and have never seen any solution/add-on, but man, I’d think this would be so useful to people. If there’s a tool you will have made my day.