About Us

Tom Ogilvy

Apparent solution provided.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

--
Regards,
Tom Ogilvy

"YellowBird" wrote:

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.

--

Thanks,
SHD

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"YellowBird" wrote:

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

--
SHD

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Search and calculate formula	Xina5280	Excel Worksheet Functions	4	February 6th 08 10:09 PM
VBA search code	Gustavo Strabeli	Excel Programming	1	March 13th 06 08:36 PM
Search Code	Soniya	Excel Programming	3	October 18th 04 12:49 PM
Excel XP VBA code to search all macro code in Excel module for specific search string criteria	Ed[_18_]	Excel Programming	4	May 20th 04 02:08 PM
Excel XP VBA code to search all macro code in Excel module for specific search string criteria	Frank Kabel	Excel Programming	0	May 19th 04 08:11 PM

Menu

About Us