Question

VBA,excel任意选一片数据选出一个最接近平均值的数把这个数值染成黄色，程序该怎么写？求大神。

Answer 1

把这段代码加到 selectionchange 事件里就可以实现选择后自动了。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As Double, b As Double
Dim rng As Range, rng1 As Range
a = WorksheetFunction.Average(Selection)
b = WorksheetFunction.Max(Selection)
If Not Selection.Find(a, lookat:=xlWhole) Is Nothing Then
Selection.Find(a, lookat:=xlWhole).Interior.ColorIndex = 6
Else
For Each rng In Selection
If Abs(rng - a) < b Then
b = Abs(rng - a)
Set rng1 = rng
End If
Next
rng1.Interior.ColorIndex = 6
End If
End Sub

Answer 2

Sub a()
Dim a As Double,  b As Double
Dim rng As Range, rng1 As Range
     a = WorksheetFunction.Average(Selection)
     b = WorksheetFunction.Max(Selection)
     If Not Selection.Find(a, lookat:=xlWhole) Is Nothing Then
        Selection.Find(a, lookat:=xlWhole).Interior.ColorIndex = 6
     Else
        For Each rng In Selection
            If Abs(rng - a) < b Then
               b = Abs(rng - a)
               Set rng1 = rng
            End If
        Next
     rng1.Interior.ColorIndex = 6
     End If
End Sub