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.

Please post a small sample of your data ( Click G advanced - Manage attachments)
BTW the AND part in your CF is not necessary as there is no more than one condition to be fulfilled

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Attached is a file with the part number data.
Indeed the AND is not required. Still the conditional formatting is deleted.
The conditional formatting was applied to Column B on the Master tab and it worked fine.

Attached Files

SAMPLE.xls (280.0 KB, 7 views)

I can see that you have Office 2016, so you should save the file as Excel workbook (*.xlsx) not as Excel 97-2003 (.xls). The last mentioned file type can't handle conditional formatting in more sheets without naming.

Furthermore there is a minor fault in your formula.

Attached Files

SAMPLE_2.xlsx (77.8 KB, 9 views)

Thank you. It seems to be solved. File\options\save were set to .xlsx, but it was saving as xls.
If you have a minute can you tell me about the two "--" that you added after the = to the formula? Is there a link that explains what those do?
=--(INDEX('Cycle Analysis'!C$3:C$1215,MATCH($B3,'Cycle Analysis'!$B$3:$B$1215,0))="Y")

Answer deleted OP not interested

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I was a bit too quick here. Actually you can omit the double unary minus (--) and get the same result. The formula with -- works fine as you can see, but the same will your original formula =INDEX('Cycle Analysis'!C$3:C$1215,MATCH($B3,'Cycle Analysis'!$B$3:$B$1215,0))="Y". The problem was alone the file type. If you want an explanation of the --, here goes:

Let's say that you have the string ABC123 in A1 and you want to extract 123 (to be used in some calculation). Now you can enter (in B1) the following formula: =Right(A1,3) and get 123. Now try in C1 to enter =ISNUMBER(B1) and you will get FALSE. Try then in C1 to enter =ISTEXT(B1) and you get TRUE. So what appears as a number actually is text which means that you can't use the contents of B1 in a calculation. Now try to put a minus sign just after the equal sign in the formula in B1 so that it becomes =-RIGHT(A1,3). B1 now shows -123 and you will note that C1 (which tested if B1 is text) now shows FALSE. That is, it is now a negative number. Then try to put one more minus sign after the equal sign in the formula in B1 so that it becomes: --RIGHT(A1,3). You will now see the result 123 and observe that C1 still shows FALSE. You can change the formula in C1 to =ISNUMBER(B1) and you will see TRUE. So what is called double unary minus coerces a 'text number' to first a negative number and then to a positive. It is in a way as if you say minus times minus gives plus.

Thanks xor for taking the time to clarify.