将条件格式应用于透视表后,如果展开表,如何自动更新条件格式以将其自身应用于新的扩展透视表?
以下是我在http://yoursumbuddy.com/re-apply-excel-pivot-table-conditional-formatting/上的帖子的逐字记录。您可能也对http://yoursumbuddy.com/unified-method-of-pivot-table-formatting/感兴趣:
该代码的关键是ModifyAppliesToRange
每种方法FormatCondtion
。该代码标识行标签范围的第一个单元格,并循环遍历该单元格中的每个格式条件,并将其重新应用于行标签范围和值范围(即第一个行中的带区)的交点形成的范围。上图。
此方法依赖于要在该第一行标签单元格中重新应用的所有条件格式。在条件格式可能不适用于最左边的行标签的情况下,我仍将其应用于该列,但修改了条件以检查其位于哪一列。
可以从SheetPivotTableUpdate事件中修改和调用此函数,因此,当用户或代码更新数据透视表时,它将自动重新应用。
Sub Extend_Pivot_CF_To_Data_Area()
Dim pvtTable As Excel.PivotTable
Dim rngTarget As Excel.Range
Dim rngSource As Excel.Range
Dim i As Long
'check for inapplicable situations
If ActiveSheet Is Nothing Then
MsgBox ("No active worksheet.")
Exit Sub
End If
On Error Resume Next
Set pvtTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
If Err.Number = 1004 Then
MsgBox "The cursor needs to be in a pivot table"
Exit Sub
End If
On Error GoTo 0
With pvtTable
'format conditions will be applied to row headers and values areas
Set rngTarget = Intersect(.DataBodyRange.EntireRow, .TableRange1)
'set the format condition's source to the first cell in the row area
Set rngSource = rngTarget.Cells(1)
With rngSource.FormatConditions
For i = 1 To .Count
'reset each format condition's range to row header and values areas
.Item(i).ModifyAppliesToRange rngTarget
Next i
End With
'display isn't always refreshed otherwise
Application.ScreenUpdating = True
End With
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句