Add a value in a cell depending on cell background color

manguy

New Member
Joined
Feb 22, 2020
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 22, 2020
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,183
Messages
5,640,673
Members
417,160
Latest member
Timon82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top