☰

Excel：自動將條件格式擴展到新增到資料上方的新行

tag-icon • tag-icon microsoft-excel conditional-formatting
Excel：自動將條件格式擴展到新增到資料上方的新行

我有很多跨多行和多列的值。它們的結構使得每一行顯示一個特定月份的數據，並且最新的值位於頂部。因此，當我用新資料更新它時，我會在現有資料上方插入一個新行。

如何確保應用於舊範圍的條件格式規則套用於新範圍（多一行）？我嘗試使用表格或命名範圍，但由於我在舊資料上方新增了行，Excel 不會自動擴展命名範圍/表格。

編輯：這是現有的數據

在此輸入影像描述

我想在此添加一月（以紅色標記）。如果每個子類別上方都有一個空白行（如第 9 行），我可以解決它，但不幸的是情況並非如此（請參見第 5 行）。

在此輸入影像描述

答案1

考慮到您的資料結構，我認為您只能使用 VBA 達到您想要的效果。我創建了類似於您的資料結構的東西，並編寫了一個每次添加行時運行的腳本。

  1. 將您的文件另存為Excel 啟用巨集的工作簿 (.xlsm)* Excel 啟用巨集的工作簿 (*.xlsm)
  2. Ctrl透過+開啟 VBA 編輯器F11
  3. 在 VBA 編輯器中，雙擊左側窗格中的工作表。
  4. 貼上此代碼：
私有子工作表_更改（ByVal 目標作為範圍）
  調暗 iTargRowsCnt 只要
  Dim oFCond 作為格式條件
  Dim sFCondRanges() 作為字串
  暗淡我只要
  Dim blnFCondRange修改為布林值
  Dim sNewAppliesTo 作為字串

  如果 Target.Address = Target.EntireRow.Address 那麼
    iTargRowsCnt = Target.Rows.Count
    對於 Me.UsedRange.FormatConditions 中的每個 oFCond
      sFCondRanges() = Split(oFCond.AppliesTo.Address, ",")
      對於 i = LBound(sFCondRanges) 至 UBound(sFCondRanges)
        如果不相交（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 = 左(sNewAppliesTo, Len(sNewAppliesTo) - 1)
        oFCond.ModifyAppliesToRange 範圍(sNewAppliesTo)
        blnFCondRangeModified = False
        sNewAppliesTo =“”
      萬一
    下一個'oFCond
  萬一

結束子

每次插入行時此程式碼都會自動運行。但使用 VBA 時要小心。儘管我試圖降低風險，但經常保存，可能會發生意想不到的事情。例如，很難設計出與撤銷功能良好配合的程式碼。在這種情況下，如果您插入行然後使用“撤消”，條件格式將會失真。

答案2

我只會堅持範圍。應用條件格式時，在頂部添加一個額外的行，然後隱藏它或在視覺上對其進行任何操作（我個人會在某個地方註明它作為緩衝區行的用途，因此它不會被錯誤地刪除）。

另一種方法是複製最上面的行，然後在新行上貼上格式（目前不在電腦上，需要驗證這是否適用於條件格式）。編輯：確認 O365 Business Plus 中的條件格式工作：Excel 2016。

答案3

使用範圍 1:1000，它可能會變更為全範圍（A1:AZ1000 或類似的值），但當您新增更多行或列時它會更新，因此它將保持全範圍

答案4

在應對類似的挑戰後，我發現只需引用“應用到：”條目中的表名稱即可自動調整表中的任何更改，包括添加行:)。

相關內容