Excel Conditional Formatting Copy to multiple rows challenges

Question

All, I've read the posts related to this question, but am unable to apply the recommendations to my document.

I have a 32 column document, and two rules apply to all 32 columns (let's start with row 2, row 1 being a header):

=IF(TYPE($A2)="I",IFBLANK($E2),0)  formats a special border which is applied to $A2:$AF2

=IF(CONCAT($H2:$R2)="",1,0) formats a light blue cell fill which is also applied to $A2:$AF2

Columns 1, 8 thru 17, and 19 have specialized formats specifically designed to fill cells with certain colors. The document was originally created in Excel 2010, with conditional formatting copied to multiple rows using the standard copy/paste which pulled the formatting to rows 3 to 900. The report has expanded to 1201 rows.

I have been trying to copy the conditional formatting to rows 3 through 1201, and the relative cell references appear not to work. Row 3 does not get =IF(TYPE($A3)="I",IFBLANK($E3,0) whether I use

> a copy/paste/paste special/format, or

> a copy/paste/paste special/all merging conditional formats, or

> copy/format painter/escape.

One MS document indicated that one might have to go to the pasted cells and correct the relative references, but that would mean 36,000 adjustments. Unlikely to be a viable solution. Am looking for a workaround.

One other anomaly appears to occur. Whenever I change the "applies to" cell ranges from absolute $A$2:$AF$2 to $A2:$AF2, after hitting OK, the conditional formatting returns the range to absolute.

I am running Office 2016, Excel 2016, with all recent updates installed.

Answer 1

Murray,

Just following up. Everything works right now. I got hung up on the command IF(CELL("type",$A2)="I",1,0) which is not correct, since the literal is not a capital "i" but an up-bar, which it took me hours to figure out.

IF(CELL("type",$A2)="|",1,0)

Thanks for your help. I can move on to other Excel challenges.

Marco Budny

ITW Powertrain Fastening

Lexington, KY

Answer 2

I think I have understood your question and there is no doubt applying relative references in conditional formatting can mess with your head!

The trick is to select all the cells to which you want the formatting to apply and then type the condition as if you were applying it to the first cell only.

So for example in your case, select A2:AF1201, then add the conditional formatting condition(s), as applied to the first cell:

eg =IF(TYPE($A2)="I",IFBLANK($E2),0) for the first condition and press enter.

Apologies if I've not captured the subtleties of the excluded columns.

Answer 3

Murray,

Thanks for the tip. I was taught how to do conditional formatting in a completely different way, and this (a) makes sense, and (b) is considerably easier.

So, to summarize:

One conditional format operates on the entire $A$2:$AF$1200 beautifully.

One conditional format for individual columns $H$2:$H$1200 through $R$2:$R$1200 also works beautifully.

But two conditional formats on columns $A$2:$A$1200 does not work, nor $U2:$1200 do not work.

And one conditional format operating on the entire $A$2:$AF$1200 does not work.

But I will give it another try tomorrow. Have a business dinner that I am already late for to attend.

BTW, I don't think it's operator error. I wonder if there are any other restrictions or conflicts. If there is any documentation that provides warnings and cautions, I would appreciate knowing where to find it to review it.

Many thanks. I'll keep you posted.