Excel工作表单元格单击选中事件，VBA动态计算同类数值总和

Excel工作表单元格单击选中事件，VBA动态计算同类数值总和(VX公众号：Excel潘谆白说VBA)

---

前言

面对每月的消费账单，面对月底待还的信用卡或花呗，面对不足三位数的余额，你是否怀疑过账单自己的消费。你是否因此开始记账，每个月记流水,想知道当年中某项消费总额;
你是一名会计，只想用粗略的查看某个月、某个科目下或某个客户经济往来的总金额入;
你是一名行政人员，想了解一下当月某个员工迟到、旷工、加班情况;
你是仓库保管人员，想查看某种类的货物当年进、出量……
不需要筛选、汇总、计算，只要点击所查单元格，VBA快速帮你汇总、计算。

---

一、运行效果

Excel工作表单元格单击选中事件，VBA动态计算数值总和

二、代码

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Dim str As String
  Dim targetUsedRow As Integer
  Dim targetUsedCol As Integer
  Dim sumTarget As String
  Dim sumAll As Integer
  ActiveSheet.Range("A1:IV65536").Interior.ColorIndex = xlNone
  str = ActiveSheet.Cells(Target.Row, Target.Column).Value
  On Error Resume Next
  For i = 1 To 65536         '计算行数
    If ActiveSheet.Cells(i, Target.Column).Value = "" Then
      targetUsedRow = i - 1
      Exit For
    End If
  Next
  For i = 1 To 65536         '计算行数
    If ActiveSheet.Cells(1, i).Value = "" Then
      targetUsedCol = i - 1
      Exit For
  End If
  Next
  If str = "" Or Target.Column > 2 Then  '若选中单元格为空白或超范围，退出触发事件
    Exit Sub
  End If
  For i = 1 To targetUsedRow
    If str <> "" And i <> Target.Row And ActiveSheet.Cells(i, Target.Column).Value = str Then
      ActiveSheet.Cells(i, Target.Column).Interior.ColorIndex = 44
      ActiveSheet.Cells(Target.Row, Target.Column).Interior.ColorIndex = 44
      sumTarget = Cells(i, 3).Value
      sumAll = sumAll + CInt(sumTarget)
    End If
  Next
  sumAll = sumAll + CInt(ActiveSheet.Cells(Target.Row, 3).Value)
  If str <> "" Then
    ActiveSheet.Cells(1, targetUsedCol + 2) = str
    ActiveSheet.Cells(1, targetUsedCol + 3) = sumAll
  End If
End Sub