subtract only the highlighted

G

ganga

i just want to how can we subtract the highlighted ones from the total.

eg:
1256 sdkjfs
1236 dfsdf
2569 dfsdfs
2669 dfsdfs
3214 dfsdf
2200 sdfsdf
1230 dfsdfsdf
(total)
(total - highlighted)

when i highlight the 2nd column each time i want the 1st column to be
subtracted from the total(all the highlighted ones).

thank you
 
G

Gary''s Student

Assuming that your data is in A1 thru B7, enter and run this macro:

Sub ganga()
c1 = 0
c2 = 0

For i = 1 To 7
If Cells(i, 2).Interior.ColorIndex = xlNone Then
c1 = c1 + Cells(i, 1).Value
Else
c2 = c2 + Cells(i, 1).Value
End If
Next

Cells(8, 1).Value = c1
Cells(9, 1).Value = c2
End Sub

This will put the sum of the non-highlighted in A8
and the sum of the highlighted in A9
 
B

Bernard Liengme

If you prefer a function to a subroutine:

Function NotLit(myrange)
For j = 1 To myrange.Count / 2
If myrange(j, 2).Interior.ColorIndex = xlNone Then
mysum = mysum + myrange(j, 1)
End If
Next j
NotLit = mysum
End Function

best wishes
 
Top