Cannot use 'cmd' to take away cells in selected range when adjusting range for conditional formattin

Occasional Contributor

Hi, I was instructed to post this here as the original Microsoft agent was unable to solve my query.

 

So I started to notice a few inaccuracies in my conditional formatting on my spreadsheet and when i checked the mange rules section i noticed that they had all been completely changed. The range for the 4 I had created had changed and become very weird and complicated with strange overlaps, and also about 20 other rules had been created which I did not make.

 

I have deleted the new ones and just need to adjust the range for the original rules, but for some reason I cannot select the area I want as I need the 'cmd' function to take away certain cells, which works when initially selecting a range but for some reason, not when selecting for a rule already created, and I don't really want to have to recreate the rules again from scratch.

6 Replies
Not much consolation but what you describe works fine on a PC. There is new functionality to duplicate a rule and I would have thought that the ability to reapply it to a new range must be supported?

@Peter Bartholomew no i tried that but duplicating includes the cell range so i would still need to edit the range of the new duplicated rule. So on a pc, when you are editing a range, (not selecting a range initially), you can select an area, then hold down (i'm guessing ctrl on a windows?), and then select an area to take away from the already selected area?

No, I didn't try that. I simply selected a new range. I can see that if you are applying the condition to a complex multi-area range, that would not be fun.
Well my issue with the most recent case is that i want it to apply to the whole of several columns except for just the top row. So when I first made the range I just highlighted all the relevant columns at the top, held down cmd and selected the top row of those columns... job done. But when I already have a rule and want to edit the range (mainly cos excel has glitched out and changed the range) when i hold cmd this time it just adds another instance of the top row rather than takes it away. And other than clicking and dragging from the 2nd row right to the bottom of the worksheet which would probably take hours if not days, I cannot think of another way to select this simple range.
The idea of removing cells from a selected range is something I was vaguely aware of, but never had cause to use. On the pc, it seems to work (using Ctrl) directly from the grid but not from conditional formatting. Something that does work is to select cells from the 2nd row and use Ctrl/Shift/Down to extend the range.

@Peter Bartholomew Ooh yes that does work! Like you said though, wouldn't be great if one had a more complex range. Seems odd that a tool they've provided, they've decided to make unavailable in other similar situations though. And then there's still just the issue of why the ranges get corrupted in the first place. I have noticed that the problem occurs when I try to insert new rows, (which I will constantly be having to do). The row below the new row gets separated into its own rule. And further complications happen if I copy and paste from the row below the new row.