Excel: How to do DRY formatting?

Question

I have some conditional formatting styles. I don't want to keep creating new rules for new ranges; I would rather follow DRY by declaring it once and referencing it elsewhere. I'm having difficulty doing this.

The conditional formatting rule works fine when it's just one range.

=Travel!$C$5:$P$8

However, I then try to add another range on another sheet, and it fails.

=Travel!$C$5:$P$8,Equipment!$B$5:$H$11

When I click "Apply", nothing happens on the spreadsheet, and the range for the rule in question resets itself to this:

=Travel!$C$5:$P$8

Is there a way to do this?

Answer 1

The same rule cannot apply to 2 different sheets at the same time. You'll have to create a separate rule for the Equipment sheet. I tried on a whim to create a named range that covered cells on 2 different sheets, then tried using that named range as the applies to range, but no luck.