Conditional formatting to change font color when value changes, allow sorting

[skyshow1]

I have a spreadsheet where I want to track if a value is changed (versus a baseline, or "original value") by changing its font color to red. To accomplish this I have copied the original data to a second sheet which I have hidden. I am using conditional formatting on the first sheet utilizing "Use a formula to determine which cells to format." The formula I am using is very simple; it just compares the values from the first sheet to the values in the second sheet, e.g. "=B2<>Sheet2!B2." Note in the example here the values in Column A cannot be changed; I only need to track changes in the numerical values.

Conditional Test.xlsx
	A	B	C	D	E
1	Direction	Col1	Col2	Col3	Col4
2	North	1	2	3	4
3	South	2	3	4	5
4	East	3	4	5	6
5	West	4	5	6	7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E5	Expression	=B2<>OrigValues!B2	text	NO

Conditional Test.xlsx
	A	B	C	D	E
1	Direction	Col1	Col2	Col3	Col4
2	North	1	2	3	4
3	South	2	3	4	5
4	East	3	4	5	6
5	West	4	5	6	7
OrigValues

This is all well and good, until the first table is sorted (e.g., largest to smallest), resulting in every value turning red. Essentially the references in the conditional formatting formulas don't "move" along with the rows. Sorted table:

Conditional Test.xlsx
	A	B	C	D	E
1	Direction	Col1	Col2	Col3	Col4
2	West	4	5	6	7
3	East	3	4	5	6
4	South	2	3	4	5
5	North	1	2	3	4
Sheet1

In summary I would like the cell references in the conditional formatting to remain pointing to the original values on sheet OrigValues, even while being sorted. I have tried using various combinations of relative vs. absolute references, including putting a separate conditional formatting condition for each row (see below for what I thought was a promising approach to solve this!), but just can't seem to get it to work properly.

Conditional Test.xlsx
	A	B	C	D	E
1	Direction	Col1	Col2	Col3	Col4
2	North	1	2	3	4
3	South	2	3	4	5
4	East	3	4	5	6
5	West	4	5	6	7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:E5	Expression	=B5<>OrigValues!B$5	text	NO
B4:E4	Expression	=B4<>OrigValues!B$4	text	NO
B3:E3	Expression	=B3<>OrigValues!B$3	text	NO
B2:E2	Expression	=B2<>OrigValues!B$2	text	NO

Any ideas on how to accomplish this? Thanks in advance for your help!

 

[Drrellik]

Welcome to the Board.

Let me ask you if you change the value from the original does it turn red? and if you change it back to the original does it change back to black?
and are you sorting the "hidden" data as well so that the values are sorted as well?

 

[Peter_SSs]

See if this CF works for you.

skyshow1.xlsm
	A	B	C	D	E
1	Direction	Col1	Col2	Col3	Col4
2	North	1	2	3	4
3	South	2	3	4	5
4	East	3	4	5	6
5	West	4	5	6	7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E5	Expression	=B2<>XLOOKUP($A2,'Original Values'!$A$2:$A$5,'Original Values'!B$2:B$5)	text	NO

Now sort (still no red since values have not changed)

skyshow1.xlsm
	A	B	C	D	E
1	Direction	Col1	Col2	Col3	Col4
2	West	4	5	6	7
3	East	3	4	5	6
4	South	2	3	4	5
5	North	1	2	3	4
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E5	Expression	=B2<>XLOOKUP($A2,'Original Values'!$A$2:$A$5,'Original Values'!B$2:B$5)	text	NO

Now change a couple of the values (the changed values show up red)

skyshow1.xlsm
	A	B	C	D	E
1	Direction	Col1	Col2	Col3	Col4
2	West	4	5	6	7
3	East	7	4	5	6
4	South	2	3	xxx	5
5	North	1	2	3	4
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E5	Expression	=B2<>XLOOKUP($A2,'Original Values'!$A$2:$A$5,'Original Values'!B$2:B$5)	text	NO

 

[skyshow1]

Welcome to the Board.

Let me ask you if you change the value from the original does it turn red? and if you change it back to the original does it change back to black?
and are you sorting the "hidden" data as well so that the values are sorted as well?

Yes, on Sheet1 any values that are changed do properly turn red, and back to black if changed back to the original values. It is not possible to sort the "hidden" data - it must remain fixed. The only problem I have is when the values are sorted on the visible sheet.

 

[Peter_SSs]

The only problem I have is when the values are sorted on the visible sheet.

Doesn't the suggestion in post #3 deal with that?

 

[skyshow1]

See if this CF works for you.

YES! Sorry, I got tied-up in meetings and wanted to test this before I replied. Thanks for the suggestion! The lesson learned for me is that conditional formatting formulas will not move with sorting, unlike other cell formatting such as the cell interior color or font type. I had not considered doing a VLOOKUP- great idea! I wish there were a simpler solution but it seems the only way to do this if I want to keep the original values on a separate sheet (without VBA). I'll mark your solution as the accepted one. Thanks again!

 

[Peter_SSs]

The lesson learned for me is that conditional formatting formulas will not move with sorting,

That is correct - CF remains with the cell, not with the data.

I wish there were a simpler solution

At least this isn't too complicated.

Thanks again!

You are welcome and thanks for the follow-up.