Excel VBA 简单实例 易学易懂

Sub 设置边框与底纹()

Dim rng As Range

Set rng = ActiveSheet.UsedRange '将已用区域赋与变量,变量的使用目录是减化代码的长度

'对已用区域向下偏移一行，以及向右偏移一列，再将两个区域的重叠区域设置边框 Application.Intersect(rng.Offset(1, 0), rng.Offset(0, 1)).Borders.LineStyle = xlContinuous '对已区域和A列的重叠区域添加底纹

Application.Intersect(rng, [a:a]).Interior.ColorIndex = 15

'对已区域和1行的重叠区域添加底纹

Application.Intersect(rng, [1:1]).Interior.ColorIndex = 15

End Sub

Sub 设置边框与底纹()

Dim rng As Range

Set rng = ActiveSheet.UsedRange

Application.Intersect(rng.Offset(1, 0), rng.Offset(0, 1)).Borders.LineStyle = xlContinuous

Union(Range("a1").Resize(rng.Rows.Count,1),Range("a1").Resize(1, rng.Columns.Count)).Select Selection.Interior.ColorIndex = 15

End Sub

Sub 选择大于100的单元格() 'Union法

Dim rng As Range, rang As Range '声明变量

For Each rng In Range("B2:I13") '遍历整个数据区域

'如果大于100则取出其地址，并与逗号串连

If rng.Value > 100 Then '如果对象变量rng大于100

If rang Is Nothing Then '如果变量rang还没有初始化

Set rang = rng '将符合条件的第一个目标赋与变量Rang

Else '否则

'合并两个对象（已查找到的区域与当前符合条件的单元格合并为一个区域）

Set rang = Union(rang, rng)

End If

End If

Next rng '判断下一个

rang.Select '选择合并区域，即所有符合条件的单元格

End Sub

Sub 颜色统计()

Dim col As Long, rng As Range, k As Integer

col = Range("a1").Interior.Color

For Each rng In Application.Intersect(ActiveSheet.UsedRange, Selection)

If rng.Interior.Color = col Then k = k + 1

Next rng

MsgBox "共有：" & k & "个", 64, "颜色统计"

End Sub

第1页下一页