VBA & Macro Problems

How to Use Select Case for Conditional Font Sizes in Excel VBA

WPS Editor Oct 10, 2026 868 views

Question details

The user needs to conditionally format font sizes based on numerical cash values using a clean, error-free VBA script instead of nested IF statements.

How to Use Select Case for Conditional Font Sizes in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Assigning different font sizes dynamically based on tiered monetary value ranges in a worksheet.
Observed behavior: Instead of using convoluted nested IF formulas, the user needs to implement a VBA Select Case statement to evaluate ascending value ranges and apply specific font sizes seamlessly.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet program to access the VBA Editor, and make sure your workbook is saved as a Macro-Enabled Workbook (.xlsm) to preserve your code.

Solution 1Recommended

Use a Select Case Statement to Change Font Sizes

Applying a VBA Select Case statement allows you to cleanly evaluate value ranges in ascending order without the mess and limitations of nested IF conditions.

A Select Case block reads much clearer than nested IFs. By checking conditions from the lowest value up to the highest, VBA evaluates each rule in order and stops at the first true condition, applying the assigned formatting.

Open the VBA Editor

Press 'Alt + F11' on your keyboard, or navigate to the Developer tab and click on the 'Visual Basic' or 'VBA Editor' button.

Insert a New Module

In the VBA editor, right-click on your workbook name in the Project Explorer panel, select 'Insert', and choose 'Module'.

Write the Select Case Code

Enter the following code to evaluate a cell (e.g., B34): Sub ConditionalFontSize() Select Case Range("B34").Value Case Is < 100000 Range("B34").Font.Size = 36 Case Is < 200000 Range("B34").Font.Size = 26 Case Else Range("B34").Font.Size = 11 End Select End Sub

Run the Macro

Close the VBA editor and press 'Alt + F8' in your spreadsheet. Select 'ConditionalFontSize' from the list and click 'Run' to apply the font sizes based on the cash value.

Use a Select Case Statement to Change Font Sizes

Ascending Order Evaluation: Always order your 'Case Is' conditions from smallest to largest when using less-than (<) logic so the VBA compiler evaluates the ranges in the correct sequence.

Powerful VBA Support

Use WPS Spreadsheet for Advanced VBA and Macros

WPS Office provides robust support for VBA and macros, allowing you to write, edit, and run complex scripts like Select Case conditional formatting seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled spreadsheet (.xlsm).
2. Access Developer Tools: Navigate to the 'Developer' tab in the top ribbon and click on 'VBA Editor'.
3. Insert and Run Code: Insert a new module, paste your Select Case formatting code, and run it using the macro manager.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats.Built-in Developer tools for creating and debugging VBA scripts.Lightweight software with a familiar, easy-to-use interface.Free to download and use for essential daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is Select Case better than nested IF statements in VBA?

Select Case makes your code significantly easier to read and maintain. Unlike nested IF statements which can become deeply indented, difficult to decipher, and prone to syntax errors, Select Case evaluates a single expression cleanly against a vertical list of conditions.

Can I apply this VBA conditional formatting to a range of multiple cells?

Yes, you can wrap the Select Case statement inside a 'For Each' loop to iterate through every cell in a specified range (e.g., Range("B2:B100")) and apply the correct font size dynamically to each cell based on its individual value.

Will this VBA macro run automatically when I change a cell value?

Not by default. To make the formatting apply automatically upon data entry, you must place the Select Case code inside a 'Worksheet_Change' event handler attached to the specific worksheet, rather than keeping it inside a standard Module.