☰

Excel：自动将条件格式扩展到数据上方添加的新行

tag-icon • tag-icon microsoft-excel conditional-formatting
Excel：自动将条件格式扩展到数据上方添加的新行

我有一堆跨多行和多列的值。它们的结构是每行显示一个特定月份的数据，最新值位于顶部。因此，当我用新数据更新它时，我会在现有数据上方插入一个新行。

如何确保应用于旧范围的条件格式规则也应用于新范围（多一行）？我尝试使用表格或命名范围，但由于我在旧数据上方添加了行，因此 Excel 不会自动扩展命名范围/表格。

编辑：这是现有数据

在此处输入图片描述

我想将 1 月（标记为红色）添加到此行。如果每个子类别上方都有一个空行（例如第 9 行），我可以解决这个问题，但不幸的是事实并非如此（参见第 5 行）。

在此处输入图片描述

答案1

考虑到您的数据结构，我认为您只能使用 VBA 来实现您想要的效果。我创建了与您的数据结构类似的内容，并编写了一个每次添加行时都会运行的脚本。

  1. 将文件另存为Excel 启用宏的工作簿 (.xlsm)* Excel 启用宏的工作簿 (*.xlsm)
  2. Ctrl通过+打开 VBA 编辑器F11
  3. 在 VBA 编辑器中，双击左侧窗格中的工作表。
  4. 粘贴此代码：
Private Sub Worksheet_Change（ByVal 目标作为范围）
  Dim iTargRowsCnt 为 Long
  Dim oFCond As FormatCondition
  Dim sFCondRanges() 作为字符串
  暗淡如长
  Dim blnFCondRangeModified 作为布尔值
  Dim sNewAppliesTo 作为字符串

  如果 Target.Address = Target.EntireRow.Address 则
    iTargRowsCnt = 目标.行.计数
    对于 Me.UsedRange.FormatConditions 中的每个 oFCond
      sFCondRanges() = Split(oFCond.AppliesTo.Address, ",")
      对于 i = LBound(sFCondRanges) 到 UBound(sFCondRanges)
        如果不是 Intersect(Me.Range(sFCondRanges(i))(1), Target(1).EntireRow.Offset(iTargRowsCnt)) 则为空
          sFCondRanges(i) = 范围(范围(sFCondRanges(i)), 范围(sFCondRanges(i)).偏移量(-iTargRowsCnt)).地址
          blnFCondRangeModified = True
        万一
      下一个
      如果 blnFCondRangeModified 则
        对于 i = LBound(sFCondRanges) 到 UBound(sFCondRanges)
          sNewAppliesTo = sNewAppliesTo & sFCondRanges(i) & ","
        下一个
        sNewAppliesTo = Left(sNewAppliesTo，Len(sNewAppliesTo) - 1)
        oFCond.ModifyAppliesToRange 范围(sNewAppliesTo)
        blnFCondRangeModified = 假
        sNewAppliesTo = ""
      万一
    下一个'oFCond
  万一

子目录结束

每次插入行时，此代码都会自动运行。但使用 VBA 时要小心谨慎。虽然我已尽力降低风险，但保存次数越多，意外情况就越有可能出现。例如，很难设计出与撤消功能配合良好的代码。在这种情况下，如果您插入行然后使用撤消，条件格式就会失真。

答案2

我会坚持使用范围。应用条件格式时在顶部添加一个额外的行，然后隐藏它或以视觉方式对其进行处理（我个人会在某处将其用途标记为缓冲区行，这样就不会被错误删除）。

另一种方法是复制最上面的行，然后将其粘贴为新行的格式（目前不在计算机上，需要验证这是否适用于条件格式）。 编辑：确认适用于 O365 Business Plus：Excel 2016 中的条件格式。

答案3

使用范围 1:1000，它可能会变为全范围（A1:AZ1000，或类似的），但它会随着您添加更多行或列而更新，因此它将保持全范围

答案4

在应对类似的挑战后，我发现只需在“应用于：”条目中引用表名，就可以自动调整表中的任何更改，包括添加行：）。

相关内容