MrExcel Publishing
Your One Stop for Excel Tips & Solutions

unhighlighting formated cells


Posted by Dennis Merritt on October 17, 2001 12:14 PM

Now Ive got a cell that has met conditional formats and is highlighted. Now I want to type in text in that box and have the highlight remove itself. Can I do this?


Posted by Aladin Akyurek on October 17, 2001 1:12 PM

See: 1822.html (NT)

Posted by Dennis on October 18, 2001 3:55 AM

Re: See: 1822.html (NT)

Thanks for the help, but it doesnt seem to work.

Posted by Dennis on October 18, 2001 3:55 AM

Re: See: 1822.html (NT)

Thanks for the help, but it doesnt seem to work.

Posted by Aladin Akyurek on October 18, 2001 7:23 AM

Re: See: 1822.html (NT)

Dennis,

I proposed using

=AND(A2>40,B2>1,OR(C2="xx",C2="yy"),NOT(ISTEXT(cell)))

where 'cell' is the cell that you wanted to turn yellow if A2>40 and B2>1 and either C2="xx" or C2="yy".

Lets say the cell that must turn yellow is D2. Now you want to add another condition (that's what I think you're after) that if D2 contains text, say the name Dennis, it then does not turn yellow.

Activate D2 and enter the above formula now with D2 in lieu of 'cell', i.e.,

=AND(A2>40,B2>1,OR(C2="xx",C2="yy"),NOT(ISTEXT(D2)))

and select yellow formatting via Format.

Fill appropriate values in A2, B2, C2 and some number in D2. Note the result. Then replace the number in D2 with Aladin and note the result.

If all this is not what you meant to have, please eloborate.

Aladin

Posted by Aladin Akyurek on October 18, 2001 7:23 AM

Re: See: 1822.html (NT)

Dennis,

I proposed using

=AND(A2>40,B2>1,OR(C2="xx",C2="yy"),NOT(ISTEXT(cell)))

where 'cell' is the cell that you wanted to turn yellow if A2>40 and B2>1 and either C2="xx" or C2="yy".

Lets say the cell that must turn yellow is D2. Now you want to add another condition (that's what I think you're after) that if D2 contains text, say the name Dennis, it then does not turn yellow.

Activate D2 and enter the above formula now with D2 in lieu of 'cell', i.e.,

=AND(A2>40,B2>1,OR(C2="xx",C2="yy"),NOT(ISTEXT(D2)))

and select yellow formatting via Format.

Fill appropriate values in A2, B2, C2 and some number in D2. Note the result. Then replace the number in D2 with Aladin and note the result.

If all this is not what you meant to have, please eloborate.

Aladin

Posted by Dennis on October 18, 2001 10:19 AM

Re: See: 1822.html (NT)

Aladin,

Sorry for being so dense. That was what I was doing wrong. Youve been a great help. Thanks a bunch!

Dennis

Posted by Dennis on October 18, 2001 10:19 AM

Re: See: 1822.html (NT)

Aladin,

Sorry for being so dense. That was what I was doing wrong. Youve been a great help. Thanks a bunch!

Dennis