Issues with CF Rules Manager

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
124
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can someone please explain to me how the CF Rule order works, it is driving me made.
I keep arranging in alphabetical column order, so the rules apply in the order left to right as I populate my sheet. When I leave and go back into the CF Rules Manager they formats rules are not in the order I put them, most strange. Do I have the power to decide the order or Excel makes this decision.
Secondly, when I am editing the range of cells to be formatted, the range appears blank in the 'Applies to' box for formats that I have already specified the range. It is very confusing trying to keep track of which formats I have applied to which range of cells.
Thirdly I seem to gain lines of formatting associated with one cell only. I keep cleaning this up, but they keep reappearing. I have even completely cleared the range of cells of all formatting as this was suggested to me. Even with this and reentering the fomat formula I keep encountering the issues mentioned
Any advice on how to resolve this would be very much appreciated. It is very frustrating. I am clearly doing something wrong, just not sure what it is.
Thank you in advance
Best Regards
Marcie Be
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
The rules should remain in the order that you set them in the rule manager. I've never seen them change on their own. If you start re-arranging your sheet then that might have some undesired effects.

Making changes to the structure of the sheet by dragging cells, or by using the Insert or Delete options from the right click menu will mess up the ranges that the rules apply to.
 

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
124
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The rules should remain in the order that you set them in the rule manager. I've never seen them change on their own. If you start re-arranging your sheet then that might have some undesired effects.

Making changes to the structure of the sheet by dragging cells, or by using the Insert or Delete options from the right click menu will mess up the ranges that the rules apply to.

@jasonb75 thank you. Your reply gives me greater confidence. In what I thought should be happening. Still doesn't explain some of the other weird stuff. I haven't been dragging cells. I have been copying established formatting across to other cells/ columns using copy past format and the ordering the rules as required. What I am expereincing has got me puzzled. I will take on board your suggestions, thanks again.
Best Regards
Marcie Be
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
Using normal paste might have the same effect, the same for paste special formats if you only paste to part of a range with an existing rule. Without setting something up to try it, I'm not sure if pasting would override the existing rules or add to them.

This could be the reason (or part of) behind the changing order as well.
 

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
124
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Using normal paste might have the same effect, the same for paste special formats if you only paste to part of a range with an existing rule. Without setting something up to try it, I'm not sure if pasting would override the existing rules or add to them.

This could be the reason (or part of) behind the changing order as well.

Thanks again@jasonb75, I think you are right. There is definetely a sequence to be followed to prevent unwanted earlier formatting remaining when new formatting is applied to the same range of cells. I am taking a column by column approach that seems to be working.
If i view the CF Rule order column by column the order is as per what I set for example Cols AN to AR over 13 rows. If I try and view the CF across the range $AN4:$AR13 this is when the order of the rules seems to go out of wack.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top