Highlighting a cell different from the formula cell

New Contributor

I am attempting to use conditional formatting in one cell (a protected cell) and have it highlight a different cell (an unprotected cell). The reason for this is I want the cell with the formula to be protected against accidental deletion/modification, and the unprotected cell is one of the cells in the formula. What I have tried to do is this:


The condition is that cell A5 is not blank, and cell A5 is greater than 4, and cell P5 is less than 1. 

The conditional formatting exists in cell R5 and the formatting is to highlight the cell. It works fine on cell R5. What I have attempted to do is have the highlight NOT apply to cell R5, but apply to cell P5 (which is part of the formula). Currently the "applies to" value is $R$5. When I change the "applies to" value of the rule to =$P$5, the whole rule disappears. I need cell P5 to be unprotected so data can be entered.


Is there a different syntax to use to accomplish this?


1 Reply

Hi @Jack_Dills2019


I hope you are well.


If I understand your question right, I would suggest that you place the Conditional formatting and formula in cell P5 where you want the cell to change if it meets the condition (click onto cell P5 and apply your formatting there).


Also for house keeping, I think the first part of your formula is unnecessary A5<>"" as the next criteria is A5>4. So unless A5>4 happens, the condition isn't met anyway. It doesn't affect your outcome though but just pointing it out.


I've attached a sample spreadsheet with the working formula.


If I have not understood you right, please let me know?


Best wishes!



Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies