ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 71534|回复: 90

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2006-11-30 18:02 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:Range对象

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

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

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

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

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

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

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

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

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

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

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

 

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

FormatCondition 对象

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

(备注:FormatConditions 是指某一个单元格中的条件格式的集合,并非所有单元格的条件格式集合)

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:06 | 显示全部楼层

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

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

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

对象

属性

Font

Bold

Color

ColorIndex

FontStyle

Italic

Strikethrough

Underline

无法使用会计下划线样式。

Border

Bottom

Color

Left

Right

Style

可使用下列边框样式(其他均不可用):xlNonexlSolidxlDashxlDotxlDashDotxlDashDotDotxlGray50xlGray75 xlGray25

Top

Weight

可使用下列边框粗细(其他均不可用):xlWeightHairline xlWeightThin

Interior

Color

ColorIndex

Pattern

PatternColorIndex

[em05]

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:08 | 显示全部楼层

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

1、 Add方法:

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

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

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

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

XlFormatConditionType 可为以下 XlFormatConditionType 常量之一。

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

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

Operator      Variant 类型,可选。条件格式运算符。可为以下 XlFormatConditionOperator 常量之一:xlBetweenxlEqualxlGreaterxlGreaterEqualxlLessxlLessEqualxlNotBetween xlNotEqual。如果 Type xlExpression,则忽略 Operator 参数。

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

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

 

[em05]

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:15 | 显示全部楼层

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 方法新建一个格式。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:24 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

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]

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:25 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

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

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]

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:33 | 显示全部楼层

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]

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:45 | 显示全部楼层

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]

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 18:47 | 显示全部楼层
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

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-30 19:02 | 显示全部楼层

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

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 07:04 , Processed in 0.064088 second(s), 9 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表