数据透视表 - 通过VBA进行条件格式化，以及值为0的空白单元格/单元格的内部颜色问题。 [英] Pivot Table - Conditional Formating via VBA and issues with the interior color of Blank cells / cells with a value of 0.
问题描述
（我首先在http://www.xtremevbtalk.com/showthread.php?t=319230发布了以下内容。到目前为止，已有48个观看次数没有回复。）
(I first posted the following at http://www.xtremevbtalk.com/showthread.php?t=319230. To date there have been 48 views with no replies.)
-------------------------------------------- -----------------------------------
-------------------------------------------------------------------------------
我遇到的问题是一个简单的（表面上）...
$
我想为条件格式编写一个VBA，它给出了一个与你得到的规则相同的规则当您使用"编辑格式规则"时Excel 2007中的界面，用于空白单元格。
通常的建议是使用：
The problem I have is a simple one (on the surface)...
I want to write a piece of VBA for Conditional Formatting which gives me a Rule identical to the one you get when you use the "Edit Formatting Rule" interface in Excel 2007, for blank cells.
The usual suggestion is to use:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(" & sF1 & "))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetLastPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions(1).ScopeType = xlDataFieldScope
1）选择一个数据透视表
2）点击"条件格式"
3）点击"管理规则..."˙
4）选择"新建"规则..." b $ b $ 5）将规则应用于表的数据范围（如果选择了整个数据透视表，则已自动选择），"选定的单元格"。
6）在"选择规则类型："下;选择"仅格式化包含"的单元格。
7）在"编辑规则说明："下。设置"仅格式化单元格："到"空白"和
8）将格式设置为"填充：" =白色。
9）检查刚刚创建的规则。
这是我希望使用VBA创建的规则。看起来很简单，但我完全感到困惑。任何帮助将不胜感激。
1) Select a Pivot Table
2) Click on "Conditional Formatting"
3) Click on "Manage Rules..."
4) Choose "New Rule..."
5) Apply Rule to Data Scope of table (already selected automatically if you selected the entire Pivot Table), "Selected Cells".
6) Under "Select a Rule type:" choose "Format only cells that contain"
7) Under "Edit the Rule Description:" set "Format only cells with:" to "Blanks"
8) Set the format to "Fill:" = White.
9) Examine the rule you have just created.
That is the rule I am looking to create using VBA. It seems so simple, but I am completely baffled. Any assistance would be appreciated.
--------------------------------- ----------------------------------------------
-------------------------------------------------------------------------------
其他信息：
看来问题归结为此，VBA代码创建的规则将Empty / Null视为等于0，而" ;单元格包含空白值"似乎知道区别。
It would appear that the issue comes down to this, the rule that the VBA code creates treats Empty/Null as equal to 0, whereas "Cell contains a blank value" seems to know the difference.
谢谢
Thank You
推荐答案
您好，
我一直在努力重现您所看到的问题。 我通过VBA看到一些奇怪的行为以及条件格式化 但我不确定我到底知道你到底是什么。 您能否提供包含步骤，结果和预期
结果的简单样本？ 例如：1）在范围A1：C2中创建一个小表，其中包含标题"A"，"B"，"C" 2）在表格中创建一个可旋转表格，其中包括A的总和，B的总和，C值的总和。 3）选择范围A2：C2，条件格式...
I've been trying to reproduce the issue you are seeing. I see some strange behavior as well with conditional formatting via VBA but am not sure I'm seeing exactly what you are. Can you provide a simple sample with steps, results and expected results? Such as: 1) Create a small table in range A1:C2 with headers "A","B","C" 2) Create a pivottable from table with Sum of A, Sum of B, Sum of C in the Values area. 3) Select range A2:C2, Conditional Formatting...
问候，
Chad
Regards,
Chad
这篇关于数据透视表 - 通过VBA进行条件格式化，以及值为0的空白单元格/单元格的内部颜色问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
