Conditional formatting option of 20 colors excel2010

New Contributor



I am trying to change a cell colors as the color names written in that cell.

For example If I write blue in A1 I want to change that cell background color to blue, if it is written red than should be red.

I can write conditional formatting formulas 20 times but the problem is the colors number will increase in time. Is it possible to get those information from a table? Whenever I add a new colors it should check from that table.


Thank you in advance.

Take care.

5 Replies
With your permission, if I may ask. What is the advantage if the text and background are the same color? ... then you don't see the text just a colored cell ... or do I understand it differently?

* Knowing the Excel version and operating system would also be an advantage.

Thank you for your understanding and patience

I know I don't know anything (Socrates)


Hi Nikolino


Thank you for your quick respond. In fact, what I exactly need is very different from that it was complicated and I just want to make it simpler.:)

What I do need is actually;

There is a "Invoice" and "customer name" columns in my excel sheet and another lets say around 20 more. What I need is, with the condition of "invoice" column is not empty I want to color entire row regarding the customer name. For example if the cell of customer name column is "Pepsi" AND at same row an invoice number has entered background colors should be blue for entire row but if in the cell entered "Coca Cola" AND again an invoice number has entered at the same row it should be red. We need this to cross check did we prepared an invoice to the customer. Because each invoice have many rows to organize better we need to color that entire rows regarding that customer name automatically.


We use windows7 and excel 2010.

Thank you for your interest.




An example file would help to understand it in more detail, please without inserting sensitive data.

Thank you for your understanding and patience

I know I don't know anything (Socrates)




You can find the excel file as attached. I already fill 1,2,3 raws but in that way it is difficult. It would great if I can get the data from a table so I can add I there will be a new customer.

In that excel file you can see that column B you can insert Customer name and in X invoice date.


I hope that file will help you to help me:))

Best Regards



Sorry for the late reply, I have not forgotten, but my obligations were very urgent.

However, here is a file that was handled with great help from Mr. Case (a very good specialist and expert). It is a binary workbook where it is actually very easy to manage.

Didn't know how to do it, now I know it too, I've learned something about your concerns.

A win-win situation for everyone :).


Attached the file.

Please always activate macros and content at the beginning, otherwise it can lead to calculation errors.

You can enter your customers in the AB column,

assign them a color and with the dropdown you can call up your customers in column B.

When retrieving, depending on the customer, the cell and row changes.


Thank you for your patience and time.


I would be happy to know if I could help.



I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.