I'm not sure I understand exactly what you want, but I'll try to help  you.

For starters, you seem to want a For loop running backwards (from 7 to
4, instead of from 4 to 7) if this is right, you shold use "For i = 7
to 4 step -1", where the "step -1" part will make it go backwards.

Secondly, the assignation line could be better like this:
Cells(9,2)=Cells(iMyRow-1,4)

The first number within Cells() is the row, and the second is the colmn.

I'm not sure why would you want the loop to start in 7 and use a "-1"
to get the 6, so I set  the loop to start in 6. If I understood what
you want to achieve, the code  should work  like this:

Dim iMyRow as integer

For iMiRow = 7 to 4 step -1
Cells(9,2)=Cells(iMyRow,4)
Next

Yes it is an odd question. Im not actually counting backward as such,
just that using the VB notation of R[i]C[4], i wasnt sure if it could
be incremented from 7 to 4 so i changed it backwards 4 to 7!
But using your 'Cells' notation is a lot easier.

This part of the code  is only the begining. Essentially i have 3 or a
range of cells. I want the sum of those cells to appear in the B9
cell. Normally this would be straight forward, but ultimately the 3
cells will depend on a criteria. So all may be summed or just 2 or
just the one depending if the meet the criteria!!!

There are several problems that I see:

- Formulas start with an equals sign. Your formula doesn't have one,
so you're just plugging a text string into the cell  and it will
presumably display
exactly that string.

- The "i" in your string is simply that - "i". You want to put 4, 5, 6, 7
in instead.
You will need to break the string and concatenate the value of i or -i
into it. I.e.
"=R[" & -i & "]C[4]" or "=R[-" & i & "]C[4]"

- Your example  doesn't match your explanation. C[4] off B9 is
column E, not column A. You could use C[-1] to grab the column to the
left of your formula cell, or simply C1 to grab column 1. Similarly,
your
-i based on B9 will give you rows 5, 4, 3 and 2 respectively for i = 4
to 7.

- And last ... even if all this was correct, you would plug four formulas
into
the same cell in very quick succession. So ... it will show all four
items,
but far too quickly for you to see the first three.