Excel: Copy a Cell Value When Its Fill Color Changes
Question details
The user needs to copy a cell value after the cell's fill color changes in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A cell is manually formatted with a different fill color, and the user wants that formatting change to trigger a formula or macro that copies the cell value elsewhere.
- Observed behavior
- Changing a cell's fill color does not trigger a formula recalculation or the Worksheet_Change event, so the value is not copied automatically.
Decide whether the color is applied manually or by conditional formatting, then choose a separate status cell, drop-down, or checkbox to represent the change.
Use a status cell to trigger the value copy
Use this reliable workaround when the color change represents a business status that can be recorded separately.
Excel treats a fill-color change as formatting, not as a cell-value change. A formula or Worksheet_Change event can respond to a status value instead, while conditional formatting can display the corresponding color automatically.
In a nearby cell, such as B2, enter a status such as Changed, Approved, or Complete. You can create choices through Data > Data Validation > List.
In the destination cell, enter a formula such as =IF(B2="Changed",A2,"") to copy the value from A2 when the status cell indicates the change.
Select the source range, choose Home > Conditional Formatting > New Rule, and create a rule based on the status cell so the fill color reflects the selected status.
Use a controlled VBA process instead of monitoring manual formatting
Use this option when the workflow requires automation and users can apply the status through a macro button or controlled procedure.
Try WPS Office for Spreadsheet Status and Value Workflows
WPS Office is a free, lightweight Microsoft Office alternative with a familiar spreadsheet interface, strong Excel-format compatibility, and an easy migration path for workbooks that use formulas, status fields, and conditional formatting.
- 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheet from the home screen.
- 2. Open the workbook: Choose Open, select the Excel .xlsx file, and review the source and destination cells.
- 3. Build the status workflow: Add a status cell, use Data > Validation for a drop-down, and apply conditional formatting to show the selected status color.
Frequently Asked Questions
Why does Excel not detect a manual fill-color change?
A fill color is formatting rather than a cell value. Excel formulas and the Worksheet_Change event respond to value edits, not to most manual formatting changes.
Can a formula copy a value based directly on a cell's fill color?
Not with a standard worksheet formula. Use a separate status cell or another value-based control, then use a formula such as =IF(B2="Changed",A2,"") and apply conditional formatting for the color.
Will Worksheet_Change run when I change a cell's background color?
No. Worksheet_Change runs when a cell's contents change. It does not normally run when a user changes the cell's fill color through the formatting controls.
How can I use a checkbox to trigger the copy?
Insert a checkbox from Developer > Insert, link it to a cell such as B2, and use a formula like =IF(B2,A2,"") in the destination cell. The linked TRUE or FALSE value triggers recalculation.
Can VBA monitor color changes automatically?
There is no standard worksheet event dedicated to manual fill-color changes. VBA can check formatting when a macro runs, or you can trigger the macro from a status-cell change or a button.
