Excel Conditional Formatting Removal

[Simont485]

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?

 

[Becky]

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

 

[Simont485]

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]

Yup, afraid so. The alternative is to start again from scratch, which I'm guessing would be worse!

Which cell is it?

 

[Simont485]

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]

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?

 

[Simont485]

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!

 

[Becky]

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!