To apply conditional formatting in Excel to non-adjacent cells.
Use a comma to separate the non-adjacent cells/ranges in the selection field, but do not add a comma at the end of the selection.
- Click the first cell, or range of cells, that the conditional formatting is to be applied to.
- Create the formatting rule and click OK.
- With the cursor still in the cell or range, select Conditional formatting | Manage rules, for ‘Current selection‘ (screenshot below).
- Click the range icon next to the rule, and the first cell or range you selected will be highlighted.
- Add a comma after the first selection, and now select the next cell or range that you want to add.
- Keep adding more selections with a comma in between each.
- When done make sure your last selection is not followed by a comma (or your selections will be lost and you will have to start again).
- Click OK.
Watch out for
Don’t add a comma at the end of the selection, otherwise, when you apply the change, your selections will be lost and you will need to reselect.