[分享]VBA实现单元格条件格式的属性、方法

Aeolian-Vox

查阅了不少的关于单元格条件格式的VBA方面的资料，多数问题所涉及到的是用代码设置或者修改单元格已有的条件格式，用代码获取已有的单元格条件格式所对应的属性案例是少之又少，较为全面的资料和实际复杂案例的介绍，几乎可以说是凤毛麟角的了。

本帖把收集到的单元格条件格式资料进行了分类、整理、汇总，分享于有所需求的各位朋友。如有不妥之处，务必指正，以共勉。

本帖分享的单元格条件格式内容：

1、  单元格条件格式在VBA中的描述

2、  单元格条件格式的属性

3、  单元格条件格式可用的格式单元格的显示属性

4、  单元格条件格式的方法

5、  用VBA代码操作单元格条件格式

6、  用VBA代码快速定位含条件格式的单元格

7、  用VBA代码取得条件格式属性

8、  用VBA代码转化条件格式为真的属性值为单元格的属性值

 

1、单元格条件格式在VBA的中描述

FormatCondition 对象

代表一个条件格式。FormatCondition 对象是 FormatConditions 集合的成员。FormatConditions 集合最多可包含给定区域的三个条件格式。

（备注：FormatConditions 是指某一个单元格中的条件格式的集合，并非所有单元格的条件格式集合）

Aeolian-Vox

2、单元格条件格式的属性

3、单元格条件格式可用的格式单元格显示属性

可用 FormatCondition 对象的 Font、Border 和 Interior 属性控制已设定格式单元格的显示。条件格式对象模型不支持这些对象的某些属性。下表中列出所有可使用条件格式的属性。

对象	属性
Font	Bold Color ColorIndex FontStyle Italic Strikethrough Underline 无法使用会计下划线样式。
Border	Bottom Color Left Right Style 可使用下列边框样式（其他均不可用）：xlNone、xlSolid、xlDash、xlDot、xlDashDot、xlDashDotDot、xlGray50、xlGray75 和 xlGray25。 Top Weight 可使用下列边框粗细（其他均不可用）：xlWeightHairline 和 xlWeightThin。
Interior	Color ColorIndex Pattern PatternColorIndex

[em05]

Aeolian-Vox

4、单元格条件格式的方法

1、 Add方法：

添加新的条件格式。返回 FormatCondition 对象，该对象代表新添加的条件格式。

expression.Add(Type, Operator, Formula1, Formula2)

expression      必需。该表达式返回一个 FormatConditions 对象。

Type      XlFormatConditionType 类型，必需。指定条件格式是基于单元格值，还是基于表达式。

XlFormatConditionType 可为以下 XlFormatConditionType 常量之一。

xlCellValue 基于单元格值的条件格式。

xlExpression 基于表达式的条件格式。

Operator      Variant 类型，可选。条件格式运算符。可为以下 XlFormatConditionOperator 常量之一：xlBetween、xlEqual、xlGreater、xlGreaterEqual、xlLess、xlLessEqual、xlNotBetween 或 xlNotEqual。如果 Type 为 xlExpression，则忽略 Operator 参数。

Formula1      Variant 类型，可选。与条件格式相关的表达式或数值。可为常量、字符串、单元格引用或公式。

Formula2      Variant 类型，可选。当 Operator 为 xlBetween 或 xlNotBetween 时，为与条件格式第二部分相关的表达式或数值（否则，则忽略本参数）。可为常量、字符串、单元格引用或公式。

 

[em05]

Aeolian-Vox

1、 Delete方法

更改现有条件格式。

expression.Modify(Type, Operator, Formula1, Formula2)

expression      必需。该表达式返回一个 FormatCondition 对象。

Type     XlFormatCondition 类型，必需。指定条件格式是基于单元格值还是基于表达式。

XlFormatCondition 可为以下 XlFormatCondition 常量之一。

xlCellValue

xlExpression

Operator     XlFormatConditionOperator 类型，可选。条件格式操作符。

XlFormatConditionOperator 可为以下 XlFormatConditionOperator 常量之一。

xlBetween

xlEqual xlGreater xlGreaterEqual xlLess xlLessEqual xlNotBetween xlNotEqual 如果 Type 为 xlExpression，则忽略 Operator 参数。

Formula1      Variant 类型，可选。与条件格式相联系的表达式或数值。可为常量、字符串、单元格引用或公式。

Formula2      Variant 类型，可选。与条件格式相联系的表达式或数值。可为常量、字符串、单元格引用或公式。

用 Add 方法新建一个条件格式时，如果试图为单个区域创建三个以上的条件格式，则 Add 方法失效。如果一个区域有三个格式，请使用 Modify 方法对这些格式进行修改，或者使用 Delete 方法删除某个格式，然后使用 Add 方法新建一个格式。

Aeolian-Vox

5、用VBA代码操作单元格条件格式

A、增加条件格式

'本示例向单元格区域 B1: B5 中添加条件格式

Sub Add_FormatCondition()

Sheet1.Range("B1:B5").FormatConditions.Delete

With Sheet1.Range("B1:B5").FormatConditions.Add(xlCellValue, xlGreater, "=$a$1")

    With .Borders

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = 6

    End With

    With .Font

        .Bold = True

        .ColorIndex = 3

    End With

End With

End Sub

效果如下图演示：

 

[em05]

Aeolian-Vox

另外：一个复杂的设置条件格式的代码，包括单元格字体颜色、单元格边框、单元格底色、单元格图案。

Sub Set_FormatConditions()

'设置条件格式2006-11-24

    Dim i%, n%, m%

    Dim Rng_Format_Format As FormatConditions

    Application.ScreenUpdating = False

    Set Rng_Format = Range("E1:E10").FormatConditions

    Rng_Format.Delete

    Rng_Format.Add Type:=xlExpression, Formula1:="=$G$5=5"

    With Rng_Format(1).Font

        .ColorIndex = 3

    End With

    m = 1

    For i = -4160 To -4107

        Select Case m

        Case 1

            n = 0

        Case 2

            n = 7

        Case 3

            n = 20

        Case 4

            n = 23

        Case Else

            Exit For

        End Select

        i = i + n

        m = m + 1

        '设置单元格条件格式成立，边框样式

        With Rng_Format(1).Borders(i)

            .LineStyle = xlContinuous

            .Weight = xlThin

            .ColorIndex = 7

        End With

    Next

    '设置单元格条件格式成立，单元格内部样式

    With Rng_Format(1).Interior

        .ColorIndex = 35    '单元格底色

        .PatternColorIndex = 5    '单元格内部图案色

        .Pattern = xlLightHorizontal    '单元格图案样式

    End With

    Application.ScreenUpdating = True

End Sub

[em05]

Aeolian-Vox

B、修改条件格式

'本示例更改单元格区域 B1: B5 的现有条件格式

Sub Modify_FormatCondition()

On Error Resume Next '避免没有条件格式的单元格

    Sheet1.Range("B1:B5").FormatConditions(1).Modify xlCellValue, xlLess, "=$a$1"

End Sub

效果如下图演示，注意第一个演示中的条件为大于，修改后的为小于：

 

C、  删除条件格式

Sub Del_FormatConditions()

'删除全部单元格的条件格式

On Error Resume Next '避免没有条件格式的单元格

    ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Delete

’可以删除指定某一个区域单元格的条件格式

End Sub

[em05]

Aeolian-Vox

6、用VBA代码快速定位含条件格式的单元格

l         选择全部含条件格式的单元格

Sub Select_All_FormatConditions()

On Error Resume Next '避免没有条件格式的单元格

    Cells.SpecialCells(xlCellTypeAllFormatConditions).Select

End Sub

l         选择指定区域含条件格式的单元格

Sub Select_Part_FormatConditions()

On Error Resume Next '避免没有条件格式的单元格

    'Columns(2).SpecialCells(xlCellTypeAllFormatConditions).Select

    Range("E1:E10").SpecialCells(xlCellTypeAllFormatConditions).Select

End Sub

l         选择指定区域含相同条件格式的单元格

Sub Select__Sameness_FormatConditions()

'以最前面的区域，做为优先选择

On Error Resume Next '避免没有条件格式的单元格

    Range("B1:F15").SpecialCells(xlCellTypeSameFormatConditions).Select

End Sub

  以上方法定位得到的区域，可以用对定义的单元格对象变量进行赋值操作：

Sub Set_FormatConditions_Evaluate()

'条件格式对象变量赋值

On Error Resume Next '避免没有条件格式的单元格

    Dim Rng As Range

    Set Rng = Cells.SpecialCells(xlCellTypeAllFormatConditions)

End Sub

效果如下图演示：

[em05]

Aeolian-Vox

7、用VBA代码取得条件格式属性
'请在立即窗口下验证结果。
Sub Gain_FormatConditions_Setting() '获取条件格式的相关条件。注意容错处理
    On Error Resume Next
    Dim Rng As Range, t_Rng As Range
    Set Rng = Cells.SpecialCells(xlCellTypeAllFormatConditions)
    For Each t_Rng In Rng
        '获取条件格式1中的表达式类型
        Debug.Print t_Rng.FormatConditions(1).Type
        '获取条件格式1中的表达式中的操作符类型
        Debug.Print t_Rng.FormatConditions(1).Operator
        '1为单元格值；2为单元格公式
        '返回条件格式1中的条件1的表达式1字串
        Debug.Print t_Rng.FormatConditions(1).Formula1
        '返回条件格式1中的条件1中的表达式2字串,
        Debug.Print t_Rng.FormatConditions(1).Formula2
        '返回条件格式1中的单元格字体色
        Debug.Print t_Rng.FormatConditions(1).Font.ColorIndex
        '返回条件格式1中的单元格填充色
        Debug.Print t_Rng.FormatConditions(1).Interior.ColorIndex
        '返回条件格式1中的单元格图案色
        Debug.Print t_Rng.FormatConditions(1).Interior.PatternColorIndex
        '返回条件格式1中的单元格图案样式索引
        Debug.Print t_Rng.FormatConditions(1).Interior.Pattern
        '返回条件格式1中的字体属性信息
        With t_Rng.FormatConditions(1).Font
            Debug.Print .Bold    '加粗
            Debug.Print .Italic    '斜体
            Debug.Print .Underline    '下划线
            Debug.Print .Strikethrough    '删除线
        End With
        '返回条件格式1中的单元格边框左边框线信息
        With t_Rng.FormatConditions(1).Borders(-4131)
            Debug.Print .LineStyle    '线条样式
            Debug.Print .Weight    '线条宽度
            Debug.Print .ColorIndex    '线条颜色
        End With
        '返回条件格式1中的单元格边框右边框线信息
        With t_Rng.FormatConditions(1).Borders(-4152)
            Debug.Print .LineStyle    '线条样式
            Debug.Print .Weight    '线条宽度
            Debug.Print .ColorIndex    '线条颜色
        End With
        '返回条件格式1中的单元格边框上边框线信息
        With t_Rng.FormatConditions(1).Borders(-4160)
            Debug.Print .LineStyle    '线条样式
            Debug.Print .Weight    '线条宽度
            Debug.Print .ColorIndex    '线条颜色
        End With
        '返回条件格式1中的单元格边框下边框线信息
        With t_Rng.FormatConditions(1).Borders(-4131)
            Debug.Print .LineStyle    '线条样式
            Debug.Print .Weight    '线条宽度
            Debug.Print .ColorIndex    '线条颜色
        End With
    Next
End Sub

Aeolian-Vox

8、用VBA代码转化条件格式为真的属性值为单元格的属性值
Sub Hold_FormatConditions_Result()
'转化条件格式成立，保留单元格条件格式属性的结果
'1、单元格内部颜色属性
'2、单元格字体属性
'3、单元格边框样式属性
'4、单元格底纹样式属性
On Error Resume Next '避免没有条件格式的单元格
 Application.ScreenUpdating = False
    Dim s_Operator(8) '存放操作符的数组
    Dim Rng As Range, t_Rng As Range
    Dim t_Rng_Val '含条件格式单元格的值
    Dim Operator_sTr% '操作符类型对应的序号
    Dim V_Fc_1, V_Fc_2 '表达式1、2中的结果
    Dim t_V_Fc_a, t_V_Fc_b '临时变量
    Dim s_Strs, s_Str '操作符
    Dim ans As Boolean '判断条件成立与否的变量
    Dim Con%, n%, i%
    Dim s1 As Object '条件格式中的单元格字体
    Dim s2 As Object '条件格式中的单元格内部
    Dim s3 As Object '条件格式中的单元格边框
    s_Operator(1) = "=And(vCell>=For1,vCell<=For2)"    'Between
    s_Operator(2) = "=Not(And(vCell>=For1,vCell<=For2))"       'NotBetween
    s_Operator(3) = "=vCell=For1"               '=
    s_Operator(4) = "=vCell<>For1"              '<>
    s_Operator(5) = "=vCell>For1"               '>
    s_Operator(6) = "=vCell<For1"               '<
    s_Operator(7) = "=vCell>=For1"              '>=
    s_Operator(8) = "=vCell<=For1"              '<=
    Set Rng = Cells.SpecialCells(xlCellTypeAllFormatConditions)
    For Each t_Rng In Rng
        n = t_Rng.FormatConditions.Count '获取含单元格的条件格式总数
        If n > 0 Then
            Con = 0
            For i = n To 1 Step -1
                With t_Rng
                    t_Rng.Select '此语句是为了调试方便留下的，可以根据情况删除
                    If .FormatConditions(i).Type = 1 Then '条件单元格为值类型
                        t_Rng_Val = t_Rng.Value '取得含条件格式单元格的值
                        Operator_sTr = .FormatConditions(i).Operator '返回该条件格式的操作符
                        '返回该条件格式中的条件表达式1
                        V_Fc_1 = Application.Evaluate(.FormatConditions(i).Formula1)
                        '操作符为介于或者不介于
                        If Operator_sTr = 1 Or Operator_sTr = 2 Then
                         '返回该条件格式中的条件表达式2
                            V_Fc_2 = Application.Evaluate(.FormatConditions(i).Formula2)
                            '单元格值、条件格式表达1的值、条件格式表达2的值是不为数值类型
                            If Not (IsNumeric(t_Rng_Val)) Or Not (IsNumeric(V_Fc_1)) Or Not (IsNumeric(V_Fc_2)) Then
                                '为空值，则转换为 "" 类型
                                If IsEmpty(t_Rng_Val) Then t_Rng_Val = ""
                                '为数值，则转换为字符类型
                                If IsNumeric(t_Rng_Val) Then t_Rng_Val = CStr(t_Rng_Val)
                                '表达式1为空值，则转换为 "" 类型
                                If IsEmpty(V_Fc_1) Then V_Fc_1 = ""
                                '表达式1为数值，则转换为字符类型
                                If IsNumeric(V_Fc_1) Then V_Fc_1 = CStr(V_Fc_1)
                                '表达式2为空值，则转换为 "" 类型
                                If IsEmpty(V_Fc_2) Then V_Fc_2 = ""
                                '表达式2为空值，则转换为字符类型
                                If IsNumeric(V_Fc_2) Then V_Fc_2 = CStr(V_Fc_2)
                            Else
                                If IsEmpty(t_Rng_Val) Then t_Rng_Val = 0
                                If IsEmpty(V_Fc_1) Then V_Fc_1 = 0
                                If IsEmpty(V_Fc_2) Then V_Fc_2 = 0
                            End If