Automatic color based on color in other cell

**Bramjonker** · 05-22-2013, 04:17 PM

Hi everyone,

I am working an a schedule for our employees that contains a lot of colors. I am looking for a way to automatically fill certain cells by formula. The list is long so conditional formatting would not really work. Or maybe with the correct formula. If possible I would like to avoid Macro or VBA. Please find my problem below:

In column A: the names of the employees
In column B: The corresponding colours
In column F: The hours when who is working
In column G: The employee who is working during the defined hoursFormatting colors.xls

It would be great if there is a way that a formula does the following: Based on the name in In column A, and the corresponding color in In column B, the name in G recieves automatically the fill color as defined in In column B. In the attachment the exel file with a small sample. Our list of employees contains over 300 with all different colors. Putting them in manually in conditional formatting would be a huge workload. In the tap "Problem" it is show how it is and in the tab "Solving" how it should become (automatically).

Any help is highly appreciated.

Best regards,
Bram

**JBeaucaire** · 05-22-2013, 04:41 PM

Rule of Excel it appears no one has brought to you before... Color is not data. Colors are for quick human reference by eye. In Excel, use actual data to flag data in a way that Excel can use quickly. Then convert your results to colors if you really need that.

Having looked more carefully, I would employ VBA to accomplish what you're doing. IS VBA ok?

**JBeaucaire** · 05-22-2013, 04:51 PM

If VBA is ok, this macro will do what you've asked.

1) Right-click on the sheet tab name and select VIEW CODE

2) Insert this macro into the sheet module that appears:

Please Login or Register  to view this content.

3) Close the VBA editor and save your workbook.

4) Now any changes in column G will trigger a name check in column A. If the name is found the adjacent color is copied onto the cell you just edited.