subtract only the highlighted

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ganga - 05 Mar 2007 12:24 GMT

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

Reply to this Message

Gary''s Student - 05 Mar 2007 17:11 GMT

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

Signature

Gary''s Student
gsnu200709

> i just want to how can we subtract the highlighted ones from the total.
>
[quoted text clipped - 13 lines]
>
> thank you

Reply to this Message

Bernard Liengme - 05 Mar 2007 17:43 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>i just want to how can we subtract the highlighted ones from the total.
>
[quoted text clipped - 13 lines]
>
> thank you

Reply to this Message

ganga - 13 Mar 2007 02:41 GMT

i have no idea how to do a subroutine..please be more specific..

thanks

> If you prefer a function to a subroutine:
>
[quoted text clipped - 25 lines]
> >
> > thank you

Reply to this Message