数据透视表 - 通过VBA进行条件格式化，以及值为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