Highlighting a cell different from the formula cell

[Jack_Dills2019]

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

[#2]

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