Can Excel autoformatting choose the fill color automatically when I enter data in a cell?

I have a small sheet with a fair number of lines on it. When I enter a name in the first column, I would like that row to automatically fill the background color. If I entered a name that was already there, I would want to see the same color used as before. If the name has not been added to the column before, I would want a new color to be picked.

Untitled 1.xls

I know I can use conditional formatting, but entering a new format every time I entered a new name will get tedious quickly.

Maybe I could use a formula to sum all the ascii values of the letters in a name and use that for the color number?

Is this possible?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile showing the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

This would need VBA - shall I move the thread for you?

Thanks. Please address both issues in post #2 whilst I move the thread.

AliGW: can I post a solution as version is not relevant with VBA?

The OP has updated their version - still using Excel 2003, it seems.

Please Login or Register  to view this content.

NOTE: without checking ALL previous used colours, there is no guarantee that a colour will not be repeated.