Home

Highlighting a cell different from the formula cell

Jack_Dills2019
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:

=AND(A5<>"",A5>4,P5<1)

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?

Thanks

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!


Cheers

Damien

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies