How can I prevent Excel from adding cells to a Conditional Formatting range?

Question

This problem so annoy to me! I have requirement to work on 1000 cells and I need to apply conditional formatting rule for specific of cell.
For example: I apply formular for F4=B6<>"" (applies to=%F%4), G4=C6<>"" (applies to=%G%4) and H4=D6<>""(applies to=%H%4). When I save the file and close it. I open "manage rules" for these cell and the Applies to is automatic change to F4=B6<>"", G4=B6<>"" and H4=B6<>"" (Applies to =%F%4:%H%4)
How can I prevent automatic change my applies to and my formular?
Please help me fix that problem
Thanks!

Answer 1

First of all, I've never seen a system that uses % as reference operator, this is the $ sign. Check your system!

You have to use absolute references in your formulas too!

Switch between relative, absolute, and mixed references - Excel

If you use relative references, you must select the first of the applied range, otherwise the formula changes!

Example:

Select A2

Add a CF using this formula

=B1=""

Then apply to A1:A2

In the moment you apply the CF the formula changes to

=B1048576=""

This behavior is normal if you use relative references, there is no issue if you use absolute references.

If you need further help share your file.

Andreas.

Answer 2

Hi Andreas,

Thanks for response

Firstly, sorry about my misleading. There are no operator % in my system that just a typo mistake $ to %.
Here is my demo file for implement.
Demo file

I apply the rule for cell F6=B6<>"" (mean if B6 change the value F6 will be automatic fill color). The problem is at first I apply the rule Applies to only equals to $F$6 after I save and close it. I open again the range is change to =$F$6:$H$6.
Please help me for this issue

Thanks
Tai.

Answer 3

Hi Andreas,

That's correct Applies to when you only apply F6,
I mean to reproduce this issue, you need to applies these cell
F6:
Formular=B6<>""
Applies to=$F$6
G6:
Formular=C6<>""
Applies to=$G$6
H6:
Formular=D6<>""
Applies to=$H$6

After you apply these rule above, please save and open again. You will see the rule of 3 cells will be change

Thanks
Tai.

Answer 4

I apply the rule Applies to only equals to $F$6 after I save and close it. I open again the range is change to =$F$6:$H$6.

Hi Tai,

that sounds strange. Please download this file:

https://www.dropbox.com/scl/fi/z65oq5bg6cje12dt9cq5w/f67b367e-5401-4d4e-a2e8-ad87a45a8e19.xlsx?rlkey=y4ayt0b0dc5xplyw9hhmqwwfe&dl=1

It's your file, just the CF applied to F6 only. If I open the file I see this:

The rule is applied to F6 only, not issue.

What do you see on your system when you open the file?

Andreas.

Answer 5

Hi Tai,

I can also not reproduce that behavior. Please download my sample file again, I've added 2 CF.

If I open the file I can see this:

No issue on my side. What do you see on your system?

Andreas.