数据透视表FormatConditions ScopeType导致1004 [英] PivotTable FormatConditions ScopeType is causing 1004
本文介绍了数据透视表FormatConditions ScopeType导致1004的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在尝试设置ScopeType时,Sub的最后出现1004错误.我想将formatcondition应用于当前列中的所有活动行,所以我认为可以做到这一点.
A 1004 error occurs at the very end of the Sub, when trying to set the ScopeType. I want the formatcondition to apply to all active rows in the current column, so I thought this would do it.
Sub CreatePivot()
' Define RngTarget and RngSource as Range type variables
Dim RngTarget As Range
Dim RngSource As Range
Dim intLastCol As Integer
Dim intCntrCol As Integer
Dim ws1, ws2 As Worksheet
Dim pt As PivotTable
Dim cf As FormatCondition
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
ws2.Cells.Clear
' RngTarget is where the PivotTable will be created (ie: Sheet2, Cell B3)
Set RngTarget = ws2.Range("B3")
'Set RngTarget = ThisWorkbook.Worksheets("Sheet2").Range("B3")
' RngSource defines the Range that will be used to create the PivotTable
' ActiveWorkbook = The currently opened Workbook
' ActiveSheet = The currectly opened sheet
' UsedRange = The Range of cells with active data in them
Set RngSource = ws1.UsedRange
'Set RngSource = ActiveWorkbook.ActiveSheet.UsedRange
' Select the Range
ws1.Select
RngSource.Select
' Copy the Range into the clipboard
RngSource.Copy
' Create a new PivotTable using the RngSource defined above,
' in Excel format,
' placed at the RngTarget location,
' And name it PivotB3 just for reference if needed
ActiveWorkbook.PivotCaches.Create(xlDatabase, RngSource).CreatePivotTable RngTarget, "PivotB3"
Set pt = RngTarget.PivotTable
' Get the last used column from the data table
intLastCol = RngSource.Columns(RngSource.Columns.Count).Column
' Select the Pivot table so we can apply the conditional formats
pt.PivotSelect "", xlDataAndLabel, True
For intCntrCol = 3 To intLastCol
ws2.Select
ws2.Cells(4, intCntrCol).Select ' Select the current Sum column
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=5000" ' Set conditional format to less than 5000
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority ' Take priority over any other formats
With Selection.FormatConditions(1).Font ' Use the Font property for the next operations
.ThemeColor = xlThemeColorLight1 ' Set it to the default (if it does not meet the condition)
.TintAndShade = 0 ' Same as above
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 ' Set the background color to Yellow
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions(1).ScopeType = xlFieldsScope ' Apply the format to all rows that match "Sum of xxxx"
Next intCntrCol
End Sub
推荐答案
基于您最后一个问题我建议这种应用格式的方法:
Based on you last question I propose this method for applying your formatting:
Set ws2 = ThisWorkbook.Sheets("Sheet2")
With ws2.UsedRange
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=5000" ' Set conditional format to less than 5000
.FormatConditions(.FormatConditions.Count).SetFirstPriority ' Take priority over any other formats
With .FormatConditions(1).Font ' Use the Font property for the next operations
.ThemeColor = xlThemeColorLight1 ' Set it to the default (if it does not meet the condition)
.TintAndShade = 0 ' Same as above
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535 ' Set the background color to Yellow
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
这篇关于数据透视表FormatConditions ScopeType导致1004的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文