Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel workbook with 10 worksheets. Each sheet has a column named status. Depending on the value of status ("on time", "at risk", "completed"), I want to format the cell background with a different color.

I've written a conditional formatting rule to do this for one sheet. I know there are methods to copy this rule to the other sheets. But ... if I do that, and want to change any formatting, I will need to edit all the rules for 10 worksheets. So instead of a copy per worksheet, I'd like one rule that applies to each column on each sheet.

According to Excel documentation the rule should accept a "cell range", and I've been able to enter disconnected cell ranges from a single worksheet (two columns not side-by-side): $L:$L,$S:$S. However, when I replace $S:$S by a column reference in another worksheet: Marketing!$S:$S, this value is deleted when the conditional rule formatting dialog box closes.

I've also tried doing this by creating a named reference called "All", which contains a list of column references from each worksheete, but this reference is stripped out when I close the conditional formatting rule manager.

So just wondering if there is a way to specify, in the "Applies To" field when creating a rule, a set of columns from different worksheets.

share|improve this question

1 Answer 1

Would it be simpler to apply a format which would check the header cells as well as the column entries - then it wouldn't matter which column was the status column, e.g. select all columns A to Z and use conditional formatting ("use a formula to determine which cells to format" option) with this formula

=AND(A$1="status",A1="on time")

format as required

If you enter exactly like that you will get formatting for any "on time" entry in the "status" column

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.