VBA & Macro Problems

How to Change Colors in Excel Likert-Scale Cells using VBA

Huma Ashraf Ch Sep 25, 2026 869 views

Question details

The user wants to know if Likert-scale cell colors can be customized in Excel and how to achieve this using VBA.

How to Change Colors in Excel Likert-Scale Cells

Product: Microsoft Excel
Device & OS: not provided
Scenario: Customizing the appearance of survey data represented in a Likert scale where the default colors do not meet presentation needs.
Observed behavior: The Likert-scale colors are fixed by default during creation, requiring a post-creation adjustment to change their appearance.

Before you start

Ensure you save a backup copy of your current workbook before running macros, and verify that the Developer tab is enabled in your ribbon to access the VBA editor.

Solution 1Recommended

Recolor Likert-Scale Cells using a VBA Macro

Use this method to apply custom RGB colors to your Likert-scale cells automatically using Visual Basic for Applications (VBA).

Because default Likert-scale colors are often fixed in external add-ins or templates, VBA provides a reliable way to override these colors. By looping through the cells, you can assign an exact background color based on the cell's underlying value.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.

Add the Color-Changing Code

Paste your VBA script that loops through your Likert-scale range. The macro should check each cell's value and assign the corresponding 'Interior.Color' using RGB values (e.g., RGB(255, 0, 0) for red).

Run the Macro

Update the worksheet name and cell range in your code to match your data. Then, press 'Alt + F8', select your macro, and click 'Run'.

Save as Macro-Enabled

Go to 'File' > 'Save As', and ensure you choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown so your code is preserved.

Recolor Likert-Scale Cells using a VBA Macro

Understanding RGB Colors: RGB values range from 0 to 255. You can find the exact RGB numbers for your preferred colors by checking the 'More Colors' option under the standard Fill Color tool.

Advanced Spreadsheet Features

Customize Likert-Scale Colors Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful built-in VBA support, allowing you to seamlessly run macros to recolor your Likert-scale data just like in Microsoft Excel.

1. Open your file in WPS: Launch WPS Spreadsheet and open your document containing the Likert-scale data.
2. Access the VBA Editor: Navigate to the 'Tools' or 'Developer' tab and click on 'VBA Editor' (or simply press Alt + F11).
3. Insert and Run Macro: Insert a new module, paste your color-changing script, adjust the target cell range, and press 'Run' to instantly update the colors.
4. Save your work: Save your document as an .xlsm file to keep your custom macro accessible for future updates.

Seamless compatibility with Microsoft Excel formats (.xlsx, .xlsm, .csv)Built-in Developer tab and fully functional VBA editorLightweight software that processes complex macros quicklyFamiliar user interface for easy transition

microsoft office alternative - wps office

Frequently Asked Questions

Can I change Likert-scale colors without using VBA?

Yes, you can use Conditional Formatting as an alternative. Select your Likert-scale cells, go to 'Home' > 'Conditional Formatting' > 'New Rule', and format cells that contain specific values or text with your desired fill color.

Why did my macro disappear after closing the Excel file?

If you save your file as a standard Excel Workbook (.xlsx), macros are automatically removed. Always save your file as an Excel Macro-Enabled Workbook (.xlsm) to retain any VBA code.

How do I reverse the color changes made by the macro?

Actions performed by a VBA macro generally cannot be undone using the standard 'Undo' button (Ctrl+Z). You will need to either close the file without saving, or run a separate macro to set the cell colors back to 'xlNone' (no fill).

Excel & Data Problems