如何固定EXCEL条件格式区域且不随插入删除而改变

小面包桑

求助各位大神！

1、希望达到的效果：如下，当A列为YES时，标注黄色的列如果为空，则高亮显示：


2、我现在做的设置：
条件格式应用范围：=$D:$E,$H:$K
书写的公式：=AND($A1="YES",D1="")

3、希望解决的问题：
一旦A到K列的部分内容被复制或粘贴覆盖、或插入删除，公式与应用范围都会随之发生错位


4、目前找到可借鉴的案例：
EXCEL条件格式区域固定不随插入删除而改变解决方法 | 开心电脑网 (kaixinit.com)

5、由借鉴案例想到的公式，但是在条件格式中无效，用一个INDIRECT还行，用AND将INDIRECT连接就不行了。
=AND(INDIRECT("$A"&ROW())="YES",INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&ROW())="")

psxk3535

加个按钮用VBA吧，条件格式一键复原

psxk3535

或者你试试这个写法：=((INDIRECT("$A"&ROW())="YES")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&ROW())=""))<>0

小面包桑

psxk3535 发表于 2021-7-27 10:06
或者你试试这个写法：=((INDIRECT("$A"&ROW())="YES")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","" ...

哇塞，谢谢大神，应用的公式能够成功高亮显示了，即使复制也不会发生移动错位。
不过还有另外一个问题，公式应用的范围要如何写公式才不会发生偏移呢？

比如，我将上表，B2:C2的数据直接复制到D2:E2列，高亮显示就失效了。

经过确认，原因是公式的应用范围相应发生了改变，如下，

psxk3535

小面包桑 发表于 2021-8-20 17:12
哇塞，谢谢大神，应用的公式能够成功高亮显示了，即使复制也不会发生移动错位。
不过还有另外一个问题， ...

好像选择性粘贴，不带格式只粘贴值不会引起条件格式变化，如果是删除、插入什么的，固定条件格式范围，目前好像无解，只有使用VBA才可以吧

小面包桑

psxk3535 发表于 2021-8-20 21:14
好像选择性粘贴，不带格式只粘贴值不会引起条件格式变化，如果是删除、插入什么的，固定条件格式范围，目 ...

谢谢回复。
EM...是的呢，我再试试使用OFFSET是否可以，如果不能固定整列，换个思路看看是否能动态跟着走好了。

MuJian669

psxk3535 发表于 2021-8-20 21:14
好像选择性粘贴，不带格式只粘贴值不会引起条件格式变化，如果是删除、插入什么的，固定条件格式范围，目 ...

粘贴导致条件格式范围变化，这个真的无解吗？现在烦死了，时间一长，条件格式面目全非！

MuJian669

小面包桑 发表于 2021-8-23 10:29
谢谢回复。
EM...是的呢，我再试试使用OFFSET是否可以，如果不能固定整列，换个思路看看是否能动态跟着 ...

楼主解决了吗？

xiaolege

MuJian669 发表于 2022-9-7 14:39
粘贴导致条件格式范围变化，这个真的无解吗？现在烦死了，时间一长，条件格式面目全非！

这个有办法吗？我也一直苦恼

sshw

xiaolege 发表于 2022-9-28 10:43
这个有办法吗？我也一直苦恼

If Target.Address = Target.EntireRow.Address Then
       With Sheet4.Range("$a$2:$j$56000") '确定公式作用范围
             .FormatConditions.Delete
             .FormatConditions.Add xlExpression, Formula1:="=OR(VALUE($B3)<>VALUE($B2),$A3<>$A2)"   '确定公式
             .FormatConditions(1).StopIfTrue = False
             .FormatConditions(1).Borders.colorIndex = 5
             .FormatConditions(1).Borders.Weight = xlHairline
     End With
end if


上面的代码用来固定条件格式的作用范围。应该就是你要的。原理是如果有插入或删除行，重设作用范围。