Excel Conditional formatting issue

%3CLINGO-SUB%20id%3D%22lingo-sub-314709%22%20slang%3D%22en-US%22%3EExcel%20Conditional%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314709%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20attempting%20to%20use%20conditional%20formatting%20in%20one%20cell%20(a%20protected%20cell)%20and%20have%20it%20highlight%20a%20different%20cell%20(an%20unprotected%20cell).%20The%20reason%20for%20this%20is%20I%20want%20the%20cell%20with%20the%20formula%20to%20be%20protected%20against%20accidental%20deletion%2Fmodification%2C%20and%20the%20unprotected%20cell%20is%20one%20of%20the%20cells%20in%20the%20formula.%20What%20I%20have%20tried%20to%20do%20is%20this%3A%3C%2FP%3E%3CP%3E%3DAND(A5%26lt%3B%26gt%3B%22%22%2CA5%26gt%3B4%2CP5%26lt%3B1)%3C%2FP%3E%3CP%3EThe%20condition%20is%20that%20cell%20A5%20is%20not%20blank%2C%20and%20cell%20A5%20is%20greater%20than%204%2C%20and%20cell%20P5%20is%20less%20than%201.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20conditional%20formatting%20exists%20in%20cell%20R5%20and%20the%20formatting%20is%20to%20highlight%20the%20cell.%20It%20works%20fine%20on%20cell%20R5.%20What%20I%20have%20attempted%20to%20do%20is%20have%20the%20highlight%20NOT%20apply%20to%20cell%20R5%2C%20but%20apply%20to%20cell%20P5%20(which%20is%20part%20of%20the%20formula).%20Currently%20the%20%3CSPAN%3E%22applies%20to%22%20value%20is%20%24R%245.%26nbsp%3B%3C%2FSPAN%3EWhen%20I%20change%20the%20%22applies%20to%22%20value%20of%20the%20rule%20to%20%3D%24P%245%2C%20the%20whole%20rule%20disappears.%20I%20need%20cell%20P5%20to%20be%20unprotected%20so%20data%20can%20be%20entered.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20different%20syntax%20to%20use%20to%20accomplish%20this%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-314709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314762%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314762%22%20slang%3D%22en-US%22%3EDid%20you%20find%20it%3F%3CBR%20%2F%3EClicking%20communities%20up%20on%20the%20left!!%3CBR%20%2F%3E%3CBR%20%2F%3EAdam%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314758%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314758%22%20slang%3D%22en-US%22%3E%3CP%3ESorry.%20I%20didn't%20see%20an%20Excel%20forum.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314724%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314724%22%20slang%3D%22en-US%22%3EPlease%20also%20post%20this%20in%20the%20excel%20forum%2C%20so%20you%20don%E2%80%99t%20miss%20out%20on%20the%20excel%20gurus%20who%20is%20hangin%20around%20in%20there!!%3CBR%20%2F%3E%3CBR%20%2F%3EAdam%3C%2FLINGO-BODY%3E
Highlighted
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

3 Replies
Highlighted
Please also post this in the excel forum, so you don’t miss out on the excel gurus who is hangin around in there!!

Adam
Highlighted

Sorry. I didn't see an Excel forum.

Highlighted
Did you find it?
Clicking communities up on the left!!

Adam