|
本帖最后由 QCLi 于 2016-6-7 19:18 编辑
请大师帮忙编个程序:建筑工程检验批超标数值自动标示三角符号。感谢!
具体意思是:(H:Q列)单元格内的数值如果超过(G列)单元格时,就在超过的数值所在的单元格标示红色的三角形。三角形符号要随数据变动而变动(H:Q列的数值是要设随机数的)。
- Sub Macro1()
- Dim arr, brr, i&, j%, k&
- For Each m In ActiveSheet.Shapes
- If m.Type <> 8 Then m.Delete
- Next
- For k = 17 To 91 Step 37
- arr = Cells(k, "g").Resize(16, 11)
- ReDim brr(1 To UBound(arr), 1 To 2)
- For i = 1 To UBound(arr)
- If InStr(arr(i, 1), "±") Then
- brr(i, 1) = 0 - Val(Mid(arr(i, 1), 2))
- brr(i, 2) = Val(Mid(arr(i, 1), 2))
- ElseIf InStr(arr(i, 1), ",") Then
- x = Split(arr(i, 1), ",")
- brr(i, 1) = Application.Min(Val(x(0)), Val(x(1)))
- brr(i, 2) = Application.Max(Val(x(0)), Val(x(1)))
- Else
- brr(i, 1) = 0: brr(i, 2) = Val(arr(i, 1))
- End If
- Next
- For i = 1 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If arr(i, j) = "" Then arr(i, j) = 0
- If Not (arr(i, j) >= brr(i, 1) And arr(i, j) <= brr(i, 2)) Then
- Set rng = Cells(i + k - 1, j + 6)
- x = rng.Left: y = rng.Top
- h = rng.Height * 0.9: w = rng.Width * 0.9
- ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, x, y, w, h).Select
- Selection.ShapeRange.Fill.Transparency = 0.6
- Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
- End If
- Next
- Next
- Next
- End Sub
复制代码
|
|