Add a value in a cell depending on cell background color

[manguy]

I know that it’s quite straight forward to change a cell background color when a certain value is entered into a cell by using conditional formatting. Is there any way doing it the other way around and have a certain value displayed when a cell color is changed?

For example, if I have a range of cells A2:J100 containing no values with no background color, if the cell color in any of the cells is then changed to red, could a value of 100 be automatically displayed in that cell? Then if the cell color is changed to blue a value of 50 is displayed, if changed to green a value of 30 is displayed.

I only need three different colors, I don’t believe this can be achieved with conditional formatting and I’m not aware of any formula/function to do this but please correct me if I am wrong. Could it be achieved with a bit of VBA code?

 

[Joe4]

This gets rather tricky, as worksheet event procedure codes run on various "events" happening, such as:
- Manual update to the value of particular cells
- The selection of particular cells
- A recalculation happening anywhere on the sheet

Unfortunately, a change to the formatting of a cell does not trigger any of those events to fire.

A few people came up with some elaborate solutions that you may find helpful.
Take a look at these links:
Capturing cell color change in VBA
Calculate on Format Change

 

[manguy]

This gets rather tricky, as worksheet event procedure codes run on various "events" happening, such as:
- Manual update to the value of particular cells
- The selection of particular cells
- A recalculation happening anywhere on the sheet

Unfortunately, a change to the formatting of a cell does not trigger any of those events to fire.

A few people came up with some elaborate solutions that you may find helpful.
Take a look at these links:
Capturing cell color change in VBA
Calculate on Format Change

Thank you for advising Joe4, I had experimented with a bit of VBA code but could not get it to trigger the event when changing the cell color just as you have pointed out. This would have been a nice to have but I can manage with adding the value manually. Thanks again.