
Originally Posted by
Kenneth Hobs
It already is a macro. Maybe you want to run it multiple times?
Please add VBA code tags when you post code. Structure makes your code easier to read and to troubleshoot. You might want to add Option Explicit so that it will prompt you to Dim your variables. You can add that as an option in the VBE tools.
In a module:
[vba]Option Explicit
Sub Macro2010(ByVal Target As Range)
Dim myrange, a, b As Range, rep(150) As Integer
Dim rr As Long, x As Variant, y As Variant, i As Long
Dim s As Range, sum_nj As Variant, chg As Variant, j As Long
Dim nj As Variant, U As Variant
On Error GoTo ErrHandler
Application.EnableEvents = False
rr = Target.Row
x = [B1].Offset(rr - 1, 0)
'=======================================================
Set myrange = Range("B14:B18")
'=======================================================
Range("B14:K18").Interior.Pattern = xlNone
i = 0
'=======================================================
Columns("k").ClearContents
For Each s In myrange
y = s.Value
If y = x Then
i = i + 1
Range("B" & s.Row, "J" & s.Row).Interior.ColorIndex = 42
rep(i) = s.Row
End If
Next s
sum_nj = 0: chg = 0
For j = 1 To i
Set a = Range("H" & rep(j))
For nj = 1 To i
Set b = Range("J" & rep(nj))
Set U = Range("K" & rep(nj))
If b.Value <> a.Value Then chg = 1: b.Interior.ColorIndex = 3
If b.Value <> a.Value Then chg = 1: U.Interior.ColorIndex = 36
Next nj
Next j
'''''''
If chg <> 0 Then
For j = 1 To i
sum_nj = sum_nj + Range("G" & rep(j)).Value
Next j
End If
If sum_nj <> 0 Then Range("k" & rep(1)).Value = sum_nj
ErrHandler:
Application.EnableEvents = True
End Sub
[/vba]
Your worksheet event code is then:
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Macro2010 Target
End Sub[/vba]