Office 2016, Win 10 Pro, 64 bit
Excel Workbook has multiple tabs (worksheets), file size is 26MB. All tabs have the same list of part number data manually copied (no formulas) into cell range B3:B1215. The tabs often get sorted using filters, so the order of the data, tab-to-tab varies. To eliminate the risk of returning an incorrect format, an Index Match inside of AND was used to result in True or False and conditional formatting was functioning properly. Saving and reopening the file deletes the conditional formatting.

Conditional Format formula: =AND(INDEX('Cycle Analysis'!C$3:C$1215,MATCH($B3,'Cycle Analysis'!$B$3:$B$1215,0))="Y")

Formatted to fill cell B3 on the current tab by matching the part number on 'Cycle Analysis' and indexing column C on the 'Cycle Analysis' tab for the presence of "Y".

The formatting worked. Saving and reopening deletes the conditional formatting through.

Painted to about 8000 cells on 6 tabs and it worked fine. All of the conditional formatting with the above formula gets removed when reopening.

Troubleshooting:
Other conditional formats do not get deleted. Just the AND, INDEX, MATCH.

Too many conditional formats? Only a single cell B3 was formatted. Still the formatting was deleted upon saving and reopening.

Incompatibility? Saving to .xlsx format: file\options\save files are being saved to Excel Workbook (*.xlsx).

File opened by a PC with incompatibility issue? File is stored and opened on a single, local PC not networked.

Are there other settings to look at?


Any alternative approaches to produce the same outcome?
Please advise.