Conditional Formatting and Protected Locked Cells Not Working

I have a spreadsheet that runs an automatic formula AND has conditional formatting. When I lock the cells/column, the calculations nor the conditional formatting run. Any help?

Below is the detail of the spreadsheet:

In cell D2, I enter in a data (e.g., 12/1/2023). In cell E2, the formula will calculate 75 days from the date in cell D2 (=D2-75). The answer is 9/17/2023. In cell F2, it will calculated 90 days from the date in cell D2 (=D2-90). Answer being 9/2/2023. I have conditional formatting in both column D and E to change to a color.

Column D will highlight red if the date that is entered is 60 days or less from today's date (=AND(D1<>"",D1<=TODAY()+60,G1=""). Column E will highlight yellow if the date that is calculated is 75 days or less (=AND(E1<>"",D1<=TODAY()+75,G1=""). Both columns will loose their color if anything is put in the corresponding cell in column G. There is no conditional formatting in column F.

I want to lock/protect columns E & F so the formulas and conditional formatting don't get changed. However, when I lock it, the conditional formatting and calculations do not happen.

Any help with this problem?

See the attached workbook as suggested.

The problem was in your formula:

Formula:

Please Login or Register  to view this content.

You should not point to the header of the table.

In the file attached I also converted your table to Excel named table so your formulas will be automatically be filled, formatting also will follow.
To add a new line you go to the last cell [Ctrl] + [End] buttons pressed together and then press [TAB], you will have the counter in your first column there for you, no need to type.

Use of Excel named table is a good practice.

Here is a quick video explaining the advantages of it.
7 Reasons Why you Should use Excel Tables

Thank you for improving my workbook!

A few things that are not working in this version compared to my original.
1. When I protect the columns E, F, K, L, I am unable to TAB and add an additional row
2. When the workbook is unprotected and I add an additional row, the formulas in columns E & F autofill with #######. Columns K & L fill with R$ 0.00. In my original, the cells remain blank but the cell and formatting still run.

The issue is after I protect columns E, F, K, L, the conditional formatting calculations don't happen. Any help with that?

Thanks for letting me know about the named tables. I will use this more.

I love Excel Tables. The one issue I have with them is that they do not play well with locked cells unfortunately. You have to get really creative.

TheNateDog, as Gregb11 said locked cells are a problem and this part of protect sheets I don't use much, so...

But I'm glad that you enjoyed the discovery of Excel Tables and hope for you a happy Marry Christmas!

Some other things I've done in the past:

Put conditional formatting on the column that if ISFORMULA, then fill with grey. That way if they typed over a formula, it would not be shaded and stand out. You could also put an error message some where that if the whole table column wasn't a formula, an error message would appear.

Also, you could put Data Validation on those columns. Something like, "Text Length" must be equal to 200 and the error alert would be "Don't type in this cell". (I used 200 because I figured no one would ever enter text exactly as 200 - you could use whatever you want.) And as you expanded and changed the table info, you would not get an error in that cell.