Excel Conditional Formatting Removal

Joined
Feb 23, 2018
Messages
67
Reaction score
24
Hi

I have applied two Conditional Formatting rules to a range of cells. (Bold text and Fill colour)

I want to remove one of the conditional formatting rules from one cell in that range. But when I delete the rule it applies it to the range or deletes all the rules

Where am I going wrong?
 
Last edited:

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
When you go into Manage Rules, instead of deleting the entire rule you need click on the bit that says 'Applies to' and then edit the range to exclude the cell :)
 
Joined
Feb 23, 2018
Messages
67
Reaction score
24
It is not that easy in this case.......I have a large spread sheet and below are the ranges of cells I have applied the CF to....I only want to remove the CF for a single cell...

If I go to 'Applies to'; do I have to pick the cell out of that lot??

=$I$21,$BX$80:$DT$80,$DV$80:$FR$80,$FT$80:$HP$80,$HR$80:$JN$80,$JP$80:$LL$80,$LN$80:$NJ$80,$NL$80:$NR$80,$Y$17:$Y$26,$BW$17:$BW$26,$DU$17:$DU$26,$FS$17:$FS$26,$JO$17:$JO$26,$HQ$17:$HQ$26,$LM$17:$LM$26,$NK$17:$NK$26,$NL$17:$NR$22,$LN$17:$NJ$22,$JP$17:$LL$22,$HR$17:$JN$22,$Y$65:$Y$74,$NL$65:$NR$71,$K$17:$X$22,$K$65:$X$71,$K$27:$K$29,$K$38:$K$40,$K$49:$K$51,$K$62:$K$64,$K$81:$K$83,$K$80:$BV$80,$K$2:$NR$16,$Z$65:$BV$71,$DV$65:$FR$71,$JP$65:$LL$71,$BW$65:$BW$80,$DU$65:$DU$80,$FS$65:$FS$80,$JO$65:$JO$80,$HQ$65:$HQ$80,$LM$65:$LM$80,$NK$65:$NK$80,$K$52:$NR$61,$Z$17:$BV$22,$FT$17:$HP$22,$BX$17:$DT$22,$DV$17:$FR$22,$BX$65:$DT$71,$FT$65:$HP$71,$HR$65:$JN$71,$LN$65:$NJ$71,$K$30:$NR$37,$K$41:$NR$48,$K$84:$NR$1048576
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Yup, afraid so. The alternative is to start again from scratch, which I'm guessing would be worse!

Which cell is it?
 
Joined
Feb 23, 2018
Messages
67
Reaction score
24
Yup, afraid so. The alternative is to start again from scratch, which I'm guessing would be worse!

Which cell is it?
In this case it is cell L70. But it could apply to any cell within the ranges.

Yes starting from scratch would be a lot worse. :)
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Ah ok, I can see how that would be frustrating. I'm trying to think how you could get around it - you could name your ranges (ie instead of referring to the cells, the CF would refer to the named range), but you would still have to edit that if you needed to remove a cell from it.

Just a thought - do you want to clear all CF from these individual cells? Or just certain rules?
 
Joined
Feb 23, 2018
Messages
67
Reaction score
24
Ah ok, I can see how that would be frustrating. I'm trying to think how you could get around it - you could name your ranges (ie instead of referring to the cells, the CF would refer to the named range), but you would still have to edit that if you needed to remove a cell from it.

Just a thought - do you want to clear all CF from these individual cells? Or just certain rules?


In answer to above I only want to remove one rule and leave others.

Just for info....and a bit of a vent for me....

It is a huge spreadsheet. It is a staff rota for 40 plus staff and it has the whole year on one sheet. So it goes from row 1 to 102 and columns A to NR. I have been given the task to get it to auto fill/format/calculate for certain requirements, types of shifts and sum hours worked based on certain rules etc...(hence my other question posts)

Up til now it has all been filled in, formatted and calculated manually.(unbelievable to my mind)

Needless to say it would be easier to redesign the spreadsheet but those that fill it in it want it left the same. I have one cell per day per person with a combination on numbers and letters in a given order that I can use for calculations etc.. of course things clash so problems arise

So I am trying to make excel work around that as opposed to the other way round!
 
Last edited:

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
In answer to above I only want to remove one rule and leave others.

Ah ok. Well depending on the number of rules you have, it might be easier to remove all formatting from the cell in question, and then adding that cell to the rules you want it to keep. Could get messy after a while though.

Sounds like a frustrating situation for you! People never like change, even if it's to their advantage. Grrr!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top