Can I modify this VBA formula?

[seth777]

Here is the VBA formula:
Sub Macro1() ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],STOCK!R2C1:R19562C10,HLOOKUP(R5C3,STOCK!R1C2:R2C10,2,FALSE),FALSE)"
Range("G6").Select
End Sub


Currently it can only spit out a 1 or a 0 into column G. But if I run it, it only runs for G6. How can I make it loop from G5:G305?

What I really need is for if to color the cell in F green if it is 1 and no fill if it is 0. Is this possible?

 

[JustynaMK]

Hey seth777,

Regarding the first part - try:

Sub Macro1()
Dim i As Integer

For i = 5 To 305
Range("G" & i).Select
ActiveCell.FormulaR1C1 = _
=VLOOKUP(RC[-1],STOCK!R2C1:R19562C10,HLOOKUP(R5C3,STOCK!R1C2:R2C10,2,FALSE),FALSE)"
Next i

End Sub


Regarding the second part:
I understand that you want to format cells in column F (green fill) if the value in column G is 1? Why won't you use Conditional Formatting?
Select the whole column F > Conditional Formatting > New Rule > Use a formula to determine which cells to format > enter:
=G1=1

I hope it helps.

 

[seth777]

Because excel is funny about letting you copy colors from conditional formatting to another spreadsheet. I know a macro can be written to loop through everything without an actual formula but i'm having trouble writing it. so im just doing the best i can at the moment haha.

 

[JustynaMK]

Hey seth777, no problem & good luck! Let me know if you need a hand with further modifying your macro.