查阅了不少的关于单元格条件格式的VBA方面的资料,多数问题所涉及到的是用代码设置或者修改单元格已有的条件格式,用代码获取已有的单元格条件格式所对应的属性案例是少之又少,较为全面的资料和实际复杂案例的介绍,几乎可以说是凤毛麟角的了。
本帖把收集到的单元格条件格式资料进行了分类、整理、汇总,分享于有所需求的各位朋友。如有不妥之处,务必指正,以共勉。
本帖分享的单元格条件格式内容:
1、 单元格条件格式在VBA中的描述
2、 单元格条件格式的属性
3、 单元格条件格式可用的格式单元格的显示属性
4、 单元格条件格式的方法
5、 用VBA代码操作单元格条件格式
6、 用VBA代码快速定位含条件格式的单元格
7、 用VBA代码取得条件格式属性
8、 用VBA代码转化条件格式为真的属性值为单元格的属性值
1、单元格条件格式在VBA的中描述
FormatCondition 对象
代表一个条件格式。FormatCondition 对象是 FormatConditions 集合的成员。FormatConditions 集合最多可包含给定区域的三个条件格式。
(备注:FormatConditions 是指某一个单元格中的条件格式的集合,并非所有单元格的条件格式集合)
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 |
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 时,为与条件格式第二部分相关的表达式或数值(否则,则忽略本参数)。可为常量、字符串、单元格引用或公式。
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 方法新建一个格式。
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
效果如下图演示:
另外:一个复杂的设置条件格式的代码,包括单元格字体颜色、单元格边框、单元格底色、单元格图案。
Sub Set_FormatConditions()
'设置条件格式
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]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
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
效果如下图演示:
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
'接上贴
'表达式1、表达式2的比较
If V_Fc_1 > V_Fc_2 Then
t_V_Fc_a = V_Fc_2
t_V_Fc_b = V_Fc_1
Else
t_V_Fc_a = V_Fc_1
t_V_Fc_b = V_Fc_2
End If
Else '操作符序号大于2的情况
t_V_Fc_a = V_Fc_1
If t_Rng_Val < V_Fc_1 Then
'单元格值小于条件格式的设置的值,即条件成立的情况
ans = True
Con = i
Exit For
End If
End If
'单元格值、条件格式表达式1、条件格式表达式2的返回值:为文本时,将小写英文字符转换为大写英文字符
If Application.WorksheetFunction.IsText(t_Rng_Val) Then t_Rng_Val = """" & UCase(t_Rng_Val) & """"
If Application.WorksheetFunction.IsText(t_V_Fc_a) Then t_V_Fc_a = """" & UCase(t_V_Fc_a) & """"
If Application.WorksheetFunction.IsText(t_V_Fc_b) Then t_V_Fc_b = """" & UCase(t_V_Fc_b) & """"
'返回s_Str字符串中的操作符为:可转换一个对象或者一个值做替换操作
s_Strs = s_Operator(Operator_sTr)
s_Str = Replace(s_Strs, "For1", t_V_Fc_a)
s_Str = Replace(s_Str, "For2", t_V_Fc_b)
s_Str = Replace(s_Str, "vCell", t_Rng_Val)
'将s_Str 转换为值出现错误时
If Application.WorksheetFunction.IsError(Application.Evaluate(s_Str)) Then
Else '转换成功。则条件格式成立
ans = Application.Evaluate(s_Str)
End If
Else '条件格式为公式
If Application.WorksheetFunction.IsError(Application.Evaluate(.FormatConditions(i).Formula1)) Then
Else
ans = Application.Evaluate(.FormatConditions(i).Formula1)
Con = i
End If
End If
End With
Next
If Con > 0 Then
Set s1 = t_Rng.FormatConditions(Con).Font '条件格式中设置的字体
Set s2 = t_Rng.FormatConditions(Con).Interior '条件格式中设置的单元格内部
Set s3 = t_Rng.FormatConditions(Con).Borders '条件格式中设置的单元格边框
With t_Rng.Font '条件格式成立的单元格字体
.Bold = s1.Bold '加粗
.Italic = s1.Italic '斜体
.Underline = s1.Underline '下划线
.Strikethrough = s1.Strikethrough '删除线
.ColorIndex = s1.ColorIndex '字体颜色索引号
End With
With t_Rng
.Interior.ColorIndex = s2.ColorIndex '单元格内部颜色索引号
.Interior.Pattern = s2.Pattern '单元格内部图案
.Interior.PatternColorIndex = s2.PatternColorIndex '单元格内部图案颜色索引号
.Borders.LineStyle = s3.LineStyle '单元格边框线类型
.Borders.ColorIndex = s3.ColorIndex '单元格边框线颜色索引号
.Borders.Weight = s3.Weight '边框线宽度(粗细)
.FormatConditions.Delete '删除条件格式
End With
End If
End If
Next
Application.ScreenUpdating = False
End Sub
(备注:本过程参考修订了:http://www.vbeach.net/bbs/archiver/?tid-6471.html处的代码)
效果如下图演示:
本帖的程序测试在Excel2003下通过。引用的帮助内容出自Excel2003VBA中。希望本帖起到资料或者工具的作用,既方便自己的快速查阅,也方便了有此需求的朋友。
[em05][em10][em10][em10]
发这个贴真是费老鼻子劲了,不是断线就是发生发帖错误,再不然就是所帖子字数超过.......。痛苦的1个小时。
[em10][em10][em10][em10][em10][em10][em24][em24]對新入門者相當有用
謝謝樓主提供分享 !!!
[em23][em23][em23]
[em24][em24][em24]
[em27][em27][em27]
欢迎光临 ExcelHome技术论坛 (https://club.excelhome.net/) | Powered by Discuz! X3.4 |