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
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
41.16 40.56 0.81
41.64 40.35 (2.40)
42.75 42.05 0.70 (0.77)
42.82 42.15 1.30
41.98 41.52 (1.01)
42.53 41.93 2.09
41.01 40.44 (2.29)
42.73 41.32 3.41
Any help with some code would be greatly appreciated.