How to Change Excel Font Color Without Changing Font Size
Question details
The user wants to apply specific font styles, such as font color, bold, or italics, to cells without overriding their existing, varying font sizes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying a unified visual style or font color across multiple cells that currently have different font sizes, aiming to preserve those original sizes.
- Observed behavior
- Excel's built-in Cell Styles require a fixed font-size value between 1 and 409 points. Applying a style forces that fixed size on all selected cells, erasing their original size formatting.
Ensure the Developer tab is enabled in your spreadsheet ribbon, as you will need to create and execute a short VBA macro to bypass the default style limitations.
Use a VBA Macro to Apply Formatting
Since native cell styles force a specific font size, creating a custom macro is the most effective way to change only the desired font properties like color and weight.
When creating a cell style, Excel strictly requires a font size. If you try to leave the size blank, the format dialog will prompt you to enter a value between 1 and 409 points. To work around this, a personal macro can be used to target and modify only specific font attributes (like color, boldness, or italics) while leaving the size attribute entirely untouched.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu bar and select 'Module' to create a blank script window.
Enter a short VBA script using the Selection object. For example, use 'Selection.Font.Color = RGB(255, 0, 0)' to change the text to red, and 'Selection.Font.Bold = True' to make it bold.
Close the VBA editor. Highlight the cells you wish to format in your spreadsheet, navigate to the Developer tab, click 'Macros', select your new macro, and click 'Run'.
For future convenience, right-click the Quick Access Toolbar, select 'Customize', choose 'Macros' from the commands list, and add your new formatting macro as a one-click button.
Use Macros in WPS Spreadsheet to Preserve Font Sizes
WPS Office offers robust VBA and macro support, allowing you to seamlessly run custom scripts that format your spreadsheet cells exactly how you want without overriding existing properties.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open the document where you need to apply the custom formatting.
- 3. Access the VBA Editor: Navigate to the Developer tab and click on the 'VBA Editor' icon to open the scripting environment.
- 4. Insert the Script: Add a new module and paste your macro code that targets the Selection.Font.Color property.
- 5. Execute the Formatting: Select the target cells in your sheet and run the macro to update the colors while perfectly preserving font sizes.
Frequently Asked Questions
Why can't I just leave the font size blank in an Excel Cell Style?
Excel's format dialog mandates a numerical value between 1 and 409 points for font sizes within a Cell Style. The system architecture does not allow a 'null' or 'ignore' state for the font size property.
Can I use Conditional Formatting instead of a macro to preserve font size?
Yes. Conditional Formatting allows you to change the font color or make text bold without affecting the font size. However, this method requires setting specific logical rules rather than a simple manual application.
Will my macro work across all my Excel workbooks?
To make the macro available across all workbooks, you should save the VBA code in your Personal Macro Workbook (Personal.xlsb) rather than saving it in a specific, individual spreadsheet.
