How to add value to cell based on background color

**Anh Trinh** · 04-15-2015, 04:40 AM

Greetings,

Is there any way to add value to a cell based on its background color?
I'm current working on an excel file to assign working shift to the employees and calculate their total working hours per week.
There are three shifts each day. I 'm using conditional formatting to change the cell color when the shift name is entered, Ex: type in Swing -> cell color will change to blue, Day->red and Night->orange. The Day and Night shift last for 8 hours and Swing shift lasts for 7 hours.

Now I need to calculate the total working hours of the employees in a week. I'm trying to find a way to automatically assign the number of hours into the cell based on its color. I mean when the color of the cell is changed the number will automatically be assigned. Ex: Blue color is Swing shift so 7 hours will be assigned to the cell, red->Day->8 hrs, orange->Night->8hrs. Then I will use Sum function to sum all the values to calculate the total hours.Is there any formula or function to do so?

Above is only what I think i should do, if there are any other viable ways to complete this requirement please share with me ?

Sorry for the wall of text!

**adhawan06** · 04-15-2015, 04:43 AM

Check Below Post

http://www.excelforum.com/excel-form...-by-color.html

As mentioned in the above post, that you can not have formula based on Color in excel. A UDF is given in the above post may be it can work for you.

Cheers!!!
Anil

**Anh Trinh** · 04-15-2015, 05:00 AM

Thank you for the quick reply!

The post shows the UDF to sum colored cells but I'm looking for a function to add a number value into the cell base on its color. A number value will be added or assign to the cell whenever the color is changed. Is there any UDF to do so? I'm looking everywhere in forum but still can't find one.