Excel: Change an Arrow's Color Based on Yes or No Answers
Question details
The user needs to change an Excel diagram arrow's color according to multiple 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.
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.
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.
In Excel, press Alt+F11, select Insert > Module, and create a standard VBA module.
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
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.
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.
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.
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. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the Start menu or Applications folder.
- 2. Open the Excel workbook: Choose Open from the WPS Office home screen and select the existing .xls or .xlsx file.
- 3. Review the response data: Use WPS Spreadsheets to inspect Yes and No response cells and adjust the worksheet or chart layout as needed.

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.




