Formatting Issues

How to Apply Conditional Formatting for Multiple Cells and Musical Scales in Excel

Guest Writer Sep 28, 2026 870 views

Question details

The user needs to apply a conditional formatting rule across a large range of cells (I51:AG57) to format excluded musical notes in black, without overwriting existing conditional formatting for included notes' intervals.

How to Apply Conditional Formatting for Multiple Cells and Musical Scales in Excel

Product: Excel
Device & OS: not provided
Scenario: Formatting a fretboard or musical scale matrix where specific notes are excluded based on a selected scale listed in non-contiguous cells.
Observed behavior: Existing fixed black formatting no longer works because all cells now have conditional formatting for musical intervals, causing overlapping format conflicts.

Before you start

Ensure that your existing conditional formatting rules for musical intervals are functioning correctly, and write down the exact cell references containing your selected scale notes.

Solution 1Recommended

Use a Custom Formula to Format Excluded Notes

Create a specific formula-based conditional formatting rule that targets excluded notes, and manage the rule hierarchy to preserve existing interval colors.

When dealing with overlapping conditional formatting rules, Excel applies formats based on the order of rules in the Conditional Formatting Rules Manager. To apply a black format only to excluded notes without breaking the musical interval colors, you must use a formula that checks the cell's value against your target notes and position the rule correctly.

Select the target range

Highlight the complete fretboard or matrix range before creating the rule (e.g., select I51:AG57).

Create a new formatting rule

Go to Home > Conditional Formatting > New Rule.

Input the exclusion formula

Select 'Use a formula to determine which cells to format'. Enter a formula that checks if the active cell's value is missing from your list of included notes in cells H46, J46, L46, N46, P46, R46, and T46.

Set the cell format

Click the Format button, go to the Fill tab, select a black color for the excluded notes, and click OK.

Adjust the rule hierarchy

Navigate to Home > Conditional Formatting > Manage Rules. Use the Up and Down arrows to place your new black formatting rule either above or below your existing interval rules, depending on which rule needs priority. Check 'Stop If True' if you want to prevent lower rules from overwriting the black cells.

Use a Custom Formula to Format Excluded Notes

Absolute vs Relative References: Ensure your formula uses relative references for the cell being evaluated (e.g., I51) and absolute references (e.g., $H$46) for the scale note cells so the rule applies correctly across the entire range.

Manage Complex Conditional Formatting Easily with WPS Spreadsheet

WPS Spreadsheet provides an intuitive Conditional Formatting Rules Manager, making it easy to apply, edit, and prioritize custom formulas across multiple cell ranges for advanced tasks like musical scale grids.

1. Open your file: Open your workbook in WPS Spreadsheet and select your target range (I51:AG57).
2. Access conditional formatting: Navigate to the Home tab, click Conditional Formatting, and then select New Rule.
3. Enter the formula: Choose 'Use a formula to determine which cells to format' and input your note exclusion formula.
4. Apply black styling: Set the cell fill color to black and confirm your rule.
5. Manage rule order: Use Conditional Formatting > Manage Rules to arrange this new rule correctly among your existing interval formats.

Supports complex formula-based conditional formatting across large data rangesHighly compatible with Microsoft Excel file formats (.xlsx)Clear and intuitive Rule Manager interface for prioritizing formatting layers

microsoft office alternative - wps office

Frequently Asked Questions

How do I manage multiple conditional formatting rules on the same cells?

You can manage multiple rules by going to Home > Conditional Formatting > Manage Rules. From there, you can use the up and down arrows to change the order in which rules are applied. The top rule has the highest priority. You can also use the 'Stop If True' checkbox to halt further formatting evaluation if a specific condition is met.

Why is my formula-based conditional formatting not applying correctly to the entire range?

This usually happens due to incorrect absolute and relative cell references in your formula. Ensure the formula is written relative to the active cell (the top-left cell of your selection) when you create the rule. Use dollar signs ($) only for cells that should remain fixed, like your reference notes.

Can conditional formatting reference non-contiguous cells?

Yes. When writing your formula, you can reference individual, non-contiguous cells (like H46, J46, and L46) by combining functions like OR(), or by creating a Named Range for those specific cells to simplify your COUNTIF or MATCH formulas.

Document & File Issues