Conditional format row designation to show mismatch with column designations

[Brian Feth]

Windows 10, Excel 2010;

I have a table where the column headers match the row labels. i.e. columns beginning at B1, C1, D1 are labeled MPH001, MPH040, MPH051 etc. for 100 columns (with no pattern to the labels). The rows are labeled the same, i.e. rows A2, A3, A4 are labeled MPH001, MPH040, MPH051 etc. for 100 rows. It creates a matrix with the rows and columns labeled the same. I want to conditionally format the column headers to show a mismatch with the rows. Does B1 match A2, does C1 match A3. I tried =B$1<>$A2, that didn't work.

I can see how I could do this with vba but I'm wondering if anyone can come up with a conditional formatting formula that will do it.

 

[JackDanIce]

Hi,

In a new sheet, I entered values 1, 2, 3, 4, 5, 6 into B1:G1 and 1, 6, 3, 4, 5, 2 into A2:A7

Clicked on B1, Conditional Formatting->Use a formula to determine which cells to format

Entered formula:

=B$1<>INDIRECT("$A"&COLUMN())

Selected a fill colour for where formula returns TRUE

Applies to range:

=$B$1:$G$1

Click OK

Cells C1 and G1 are highlighted as not being equal to A3 and A7 respectively.

 

[Brian Feth]

Thank you. That works perfectly! Very clever.

 

[JackDanIce]

You're welcome