conditional formatting

[ajm]

i am setting conditional formatting to a column to highlight where it is different to another column.

Formula is: =if($AF94<>$T94,1,0)

So, if those two cells are different, answer is True. This then formats the cell RED. This is applied to the $AF$94:$AF$622.

I have then used the same formula for each subsequent pair of columns: AG<>U, AH<>V, AI<>W, AJ<>X, AK<>Y, etc

On any cell in the first few columns (AG - AI), looking at the CF by selecting Manage Rules, shows the formula from above. =if($AF94<>$T94,1,0)

However, Column AJ goes awry. it still works but i am curious why its now showing =if($aj1048142<>$x1048142,1,0)

any ideas?

 

[etaf]

that happens when the formula is not recognised or something is changed in the conditional selection - i get that quite often
BUT
you dont need the IF
=$AF94<>$T94
will work
you should also be able to select
AG to AK
and then use without the $
=AF94<>T94
but does you selection start at row 94 - ????

insights.xlsx
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
93
94
95
96													a
97
98		1	a												a
99
100
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF94:AK102	Expression	=AF94<>T94	text	NO

 

[ajm]

that happens when the formula is not recognised or something is changed in the conditional selection - i get that quite often
BUT
you dont need the IF
=$AF94<>$T94
will work
you should also be able to select
AG to AK
and then use without the $
=AF94<>T94
but does you selection start at row 94 - ????

insights.xlsx
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
93
94
95
96													a
97
98		1	a												a
99
100
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF94:AK102	Expression	=AF94<>T94	text	NO

thanks etaf. i deleted then rewrote the formula and it worked. and yes, the data in this case started at row 94. also, for soooo many years i have been writing the whole if statement. never knew that i could just use the formula part, eg AF94<>T94.

 

[etaf]

you are welcome