Chart & Visualization Issues

Excel: Change an Arrow's Color Based on Yes or No Answers

Adam DavisAdam Davis Nov 12, 2026 868 views

Question details

The user needs to change an Excel diagram arrow's color according to multiple Yes or No answers.

Excel: Change an Arrow's Color Based on Yes or No Answers
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains arrows or other shapes linked to groups of response cells, and the user wants the arrow color to summarize the responses.
Observed behavior
Excel conditional formatting can format answer cells but cannot directly change a shape's fill color based on worksheet formulas.
Before you start

Identify the response-cell ranges associated with each arrow and record the exact names of the arrow shapes. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) before adding VBA.

Solution 1Recommended

Use VBA to color each Excel arrow from its Yes and No responses

Use this approach when the arrow is a worksheet shape and its color must update from multiple response cells.

Excel conditional formatting applies to cells, not directly to the fill color of Shapes. A VBA macro can count the responses for each arrow and assign a green, red, or orange RGB color.

The example below assumes the arrow is named Arrow 1 and its responses are in B2:B5. Replace the shape name and range for each arrow in your worksheet.

1
Open the VBA editor

In Excel, press Alt+F11, select Insert > Module, and create a standard VBA module.

2
Add the arrow-color macro

Paste this code into the module: Sub UpdateArrowColor() Dim ws As Worksheet Dim shp As Shape Dim responses As Range Dim yesCount As Long, noCount As Long, cell As Range Set ws = ActiveSheet Set shp = ws.Shapes("Arrow 1") Set responses = ws.Range("B2:B5") For Each cell In responses Select Case UCase(Trim(CStr(cell.Value))) Case "YES": yesCount = yesCount + 1 Case "NO": noCount = noCount + 1 End Select Next cell If yesCount = responses.Count Then shp.Fill.ForeColor.RGB = RGB(0, 176, 80) ElseIf noCount = responses.Count Then shp.Fill.ForeColor.RGB = RGB(255, 0, 0) Else shp.Fill.ForeColor.RGB = RGB(255, 192, 0) End If End Sub

3
Run the macro

Return to Excel, press Alt+F8, select UpdateArrowColor, and click Run. The arrow becomes green when every response is Yes, red when every response is No, and orange when the responses are mixed.

4
Repeat for other arrows

Duplicate the procedure or expand it to use a mapping of shape names and response ranges. Update each Shapes("Arrow 1") and Range("B2:B5") reference to match the corresponding diagram element.

5
Update colors after responses change

Run the macro again after editing the Yes or No cells, or call UpdateArrowColor from a Worksheet_Change event in the worksheet's VBA code module if automatic updates are required.

Macro security and naming matter: Enable macros only for trusted workbooks. Shape names must match the names shown in Home > Find & Select > Selection Pane, and the response range must contain consistent Yes or No values.
Free Microsoft Office alternative

Try WPS Office for Clearer, Compatible Spreadsheet and Diagram Workflows

WPS Office is a free, lightweight Microsoft Office alternative with a familiar interface and strong compatibility with Excel XLS and XLSX files. It supports seamless migration for spreadsheet work, while advanced Excel-specific VBA shape automation may still require Microsoft Excel.

  1. 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the Start menu or Applications folder.
  2. 2. Open the Excel workbook: Choose Open from the WPS Office home screen and select the existing .xls or .xlsx file.
  3. 3. Review the response data: Use WPS Spreadsheets to inspect Yes and No response cells and adjust the worksheet or chart layout as needed.
Free and lightweight spreadsheet software for everyday chart and visualization workFamiliar interface that makes migrating from Excel straightforwardCompatible with Microsoft Excel XLS and XLSX spreadsheet formatsUseful tools for organizing Yes and No responses and presenting diagram dataSupports common Microsoft Office formats for easier file sharing
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel conditional formatting directly change an arrow's fill color?

No. Conditional formatting changes cell formatting, but it does not directly control the Fill.ForeColor.RGB property of a worksheet Shape. VBA is needed to connect cell results to a shape color.

How do I find the exact name of an Excel arrow?

Open Home > Find & Select > Selection Pane. Select the arrow in the list and use its displayed name in the VBA reference, such as Shapes("Arrow 1").

How can I show a different color when Yes and No answers are mixed?

Count the Yes and No values in the associated response range. Set the arrow to green when all values are Yes, red when all are No, and orange when both answers occur.

Why does the macro not change my arrow color?

Check that macros are enabled, the workbook is saved as .xlsm, the shape name is exact, and the VBA range points to the intended response cells. Also verify that the cells contain Yes or No values without unexpected text.

Can the arrow color update automatically when a response changes?

Yes. Place a call to UpdateArrowColor in the worksheet's Worksheet_Change event and limit the event to the relevant response range. The macro must also be stored in a macro-enabled workbook.