☰

使用公式批量编辑 Excel 条件格式结构。公式中使用的年份是 2022 年，需要更改为 2023 年

tag-icon • tag-icon microsoft-excel conditional-formatting editing
使用公式批量编辑 Excel 条件格式结构。公式中使用的年份是 2022 年，需要更改为 2023 年

我为公司地点创建了一个 17 个标签的工作表，用于跟踪与预算相关的每月绩效指标。我使用引用单元格作为条件来控制单元格的条件格式以及正常单元格值公式。其中许多都堆叠在IF年度函数中，每个单元格也有多个规则。引用单元格由数据验证列表控制，只是月份和年份的文本（例如 2022 年 7 月）。

我的问题现在可能很明显了！现在是 2023 年！当我更改数据验证参考（例如 2023 年 7 月）时，公式会出现不同的错误，并且条件格式会失败。我能够使用“查找和替换”功能更正单元格公式，但这不会编辑/更新条件格式规则。

以下是条件格式规则示例：在 12 月数据的列中，公式：=$F$4="November 2022"将字体变为浅灰色并将单元格填充为白色。单元格 F4 每月都会更改当前月份。该规则在每个月的单元格中重复，以确定字体颜色为未来月份的灰色或过去或当前月份的黑色。使用此方法，我有 36 个选项卡，其中有 40 行和 12 列。条件格式规则中的 2022 现在需要为 2023。

有没有办法批量更新规则？使用 VBA、宏还是其他什么？这一变化将影响大约 22,000 个条目，手动更新无异于自杀。

条件格式规则示例

答案1

有一个 VBA 解决方案。这将针对活动工作表进行更改：

Sub ChangeYearTo2023()
    Dim cf As FormatCondition
    For Each cf In ActiveSheet.Cells.FormatConditions
        cf.Formula1 = Replace(cf.Formula1, "2022", "2023")
    Next
End Sub

您还可以考虑更改公式，使其始终适用于当前年份。这对您的数据来说可能是好事，也可能不是好事。

Sub ChangeYearToCurrentYear()
    Dim cf As FormatCondition
    For Each cf In ActiveSheet.Cells.FormatConditions
        cf.Formula1 = Replace(cf.Formula1, "2022""", """ & YEAR(TODAY())")
    Next
End Sub

答案2

对于您未来构建的电子表格（对其他人构建的电子表格没有太多直接帮助），您始终可以将此类内容放入命名范围中，并在条件格式公式中使用命名范围。更改命名范围的值很容易做到，然后会“推广”到使用它的所有公式中。

或者使用给定的宏用新创建的命名范围替换 2022。

（当然，既然您现在有了这个不错的宏，我想它可能会成为您未来创作的关键。）

但是对于那些不允许使用宏的人来说......

虽然……这确实是一个不错的宏，宏使事情变得如此简单，但有人指出，即使在一个不允许使用宏的组织中，也不需要保存宏，只需打开电子表格，将其粘贴到模块中，使用它，然后在保存之前删除它，或者小心地将其保存为 XLSX，这样 Excel 就会帮你删除。可能不符合组织规则的精神，但你知道……不过我要指出的是，如果“法律条文与法律精神”没有得到很好的理解，我无权更换你的薪水。

相关内容