VBA Code to search and calculate.

[Guest]

Looking for some help with VBA code to do some search and calculations.

I have 4 Columns (A-B-C-D) for sake of argument. I have raw numbers in
Columns A & B and calculations in C & D. There may be anywhere from 10 to
500 Rows. Possible more. A & B may or may not be “color†filled. A with a
Lime Green and B with a Red fill.

What I need to do is search Column A from top down until I find a Green
fill. After I locate the fill, I then need to move to Column B and search
for a Red fill.

If the cell in Column B adjacent to the Green fill has a Red fill, I need to
then subtract Column B from Column A and return the results in Column C. If
there were no Red fill, the search would continue down B until a Red fill is
found and then subtract the Red cell from the Green cell and return the
results in Column D.

After subtracting the Red from the Green, the next step would be to drop one
Row and return to Column A in search of a Green fill. If nothing is found,
the search should continue down Column A until a Green fill is located. When
located, I then need to subtract the previous Red fill from Column B, Row ‘n’
from the new found Green fill Column A, Row ‘n’ and return the result in
Column D in the same Row as the Red fill.

If there is a Green “and Red fill in the same Row, the Red in Column B gets
subtracted from the Green in Column A; however, the Green would not be
subtracted from the adjacent Red. Again, the search would need to continue
down Column A until the next Green fill is located.

The search/calculations should stop once an empty cell is reached in Column
A or B.

The following is a sample/example from one of my spreadsheets. Figures in
Column C are calculated based on a Green fill in A and those in D from a Red
fill in B. (Copy/Paste did not include the color fill)

A B C D
42.43 41.35
41.72 41.05
41.35 40.82
41.16 40.56 0.81
41.48 40.88
41.64 40.35 (2.40)
42.09 41.18
42.29 41.67
42.75 42.05 0.70 (0.77)
42.53 42.22
42.63 41.67
42.82 42.15 1.30
42.75 42.07
42.46 42.00
42.38 41.85
42.40 41.64
41.98 41.52 (1.01)
42.05 41.59
42.53 41.93 2.09
42.19 41.80
42.24 41.68
42.03 40.90
41.08 40.69
41.01 40.44 (2.29)
42.04 40.88
42.11 41.64
42.45 41.93
42.73 41.32 3.41
41.62 40.60
41.05 40.28
40.99 40.32
40.65 40.26
40.94 39.32


Any help with some code would be greatly appreciated.

Thanks
SHD

 

[Guest]

Assume the Lime Green is colorindex 35 and red is 3 (adjust to match what you
are using).

Sub ABC()
Dim i As Long, j As Long
Dim lastRow As Long
i = 2
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Do While i < lastRow
If Cells(i, 1).Interior.ColorIndex <> 35 Then
i = i + 1
Else
j = i
Do While Cells(j, 2).Interior.ColorIndex <> 3 And _
j < lastrow
j = j + 1
Loop
If j >= lastRow and cells(j,2).Interior.colorIndex <> 3 _
Then Exit Sub
If i = j Then
Cells(i, 3) = Cells(i, 1) - Cells(i, 2)
Else
Cells(j, 4) = Cells(i, 1) - Cells(j, 2)
End If
i = j + 1
End If
Loop

End Sub

I couldn't follow the second part, so I don't know what you want.

 

[Guest]

Tom,

You've been of assistance to me in the past so I appreciate your help. I
believe, had the color come over with my copy/paste, it would have been
clearer (maybe). Let me try this and I'll post results unless there is a way
I can contact you directly.

Basically, I need the Sum of the first "find" in Col A (Green) subtracting
the first find in Col B (Red). 41.16 minus 40.35 = 0.81 in Col C. Then
search again for the next
find in A (Green) and subtract that from the previous Red in A. 40.35 minus
40.75 = (2.40) in Col D. This would continue until empty cells are found.

One note, the 3.41 does not show the other component of the subtraction. It
was further down the in the spreadsheet. The 40.44 (Red) subtracting the
42.73 (Green) = (2.29) was the last complete subtraction shown. The 3.41 can
be disregarded for this sample.

 

[Guest]

(e-mail address removed)

--
Regards,
Tom Ogilvy

Tom,

You've been of assistance to me in the past so I appreciate your help. I
believe, had the color come over with my copy/paste, it would have been
clearer (maybe). Let me try this and I'll post results unless there is a way
I can contact you directly.

Basically, I need the Sum of the first "find" in Col A (Green) subtracting
the first find in Col B (Red). 41.16 minus 40.35 = 0.81 in Col C. Then
search again for the next
find in A (Green) and subtract that from the previous Red in A. 40.35 minus
40.75 = (2.40) in Col D. This would continue until empty cells are found.

One note, the 3.41 does not show the other component of the subtraction. It
was further down the in the spreadsheet. The 40.44 (Red) subtracting the
42.73 (Green) = (2.29) was the last complete subtraction shown. The 3.41 can
be disregarded for this sample.

 

[Guest]

Apparent solution provided.