How to Change Excel Shape Color via Conditional Formatting on Another Sheet using VBA
Question details
The user wants to dynamically change the fill color of a shape based on the conditional formatting color applied to a specific cell located in a different worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building interactive visual dashboards or reports where shapes must dynamically reflect status colors governed by conditional formatting rules on a separate data sheet.
- Observed behavior
- Shapes cannot natively inherit conditional formatting colors from a cell. When trying to link them, only the static background color is read, requiring a VBA workaround to extract the visible conditional color.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings. Note down the exact name of your target shape and the worksheet indices or names.
Use VBA DisplayFormat to Apply Conditional Colors to Shapes
Implement a VBA macro utilizing the DisplayFormat property to read the calculated conditional formatting color from the source sheet and apply it as the RGB fill color of the shape on the target sheet.
By default, reading a cell's 'Interior.Color' in VBA only retrieves its static background color, ignoring any active conditional formatting. To capture the color currently rendered by conditional rules, you must use 'DisplayFormat.Interior.Color'.
Additionally, ensure that you explicitly reference the correct worksheet index or name for both the source cell and the target shape to prevent runtime errors.
Press 'ALT + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor. Click 'Insert' from the top menu and select 'Module' to create a new script area.
Copy and paste the following code into the module, updating the worksheet references and shape name to match your file: Sub UpdateShapeColor() ThisWorkbook.Worksheets(1).Shapes("Rec").Fill.ForeColor.RGB = ThisWorkbook.Worksheets(2).Range("D8").DisplayFormat.Interior.Color End Sub
Close the VBA editor. Go to the 'Developer' tab, click 'Macros', select 'UpdateShapeColor', and click 'Run'. The shape on Worksheet 1 will now match the conditionally formatted color of cell D8 on Worksheet 2.
Because conditional formatting changes don't trigger a standard Change event, automate the script by double-clicking 'ThisWorkbook' in the VBA Project pane and adding the code inside a 'Workbook_SheetCalculate' event. This forces the shape color to update whenever the workbook recalculates.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers powerful built-in VBA support, allowing you to create, edit, and execute complex macros like shape color formatting effortlessly. Manage your automated dashboards with a familiar interface and excellent performance.
- 1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file containing your conditional formatting data.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Visual Basic' to open the VBA editor.
- 3. Paste and Execute the Code: Insert your DisplayFormat macro, assign it to a calculation event or a button, and watch your cross-sheet shape colors update instantly.
Frequently Asked Questions
Can I link an Excel shape's color to a cell without using VBA?
No, Excel natively allows you to link a shape's text to a cell by selecting the shape and typing '=A1' in the formula bar, but it does not inherit the cell's background color or conditional formatting. VBA is strictly required for color inheritance.
Why isn't my shape color updating automatically when the cell's conditional formatting changes?
Conditional formatting updates do not trigger standard worksheet change events in Excel. You must either run your VBA macro manually after the data updates or tie the code to the 'Worksheet_Calculate' event so it triggers upon workbook recalculation.
What is DisplayFormat and why is it necessary for this task?
The 'DisplayFormat' property allows VBA to read the color that is currently visible on the screen as a result of active conditional formatting rules. Using the standard 'Interior.Color' property would only return the cell's static background color, ignoring the conditional rules entirely.
