Need help using VBA in excel.

[Guest]

The following is some VBA code being run using a Macro.

ActiveCell.Select
Selection.Interior.ColorIndex = 34
Range("G4").FormulaR1C1 = "=countbycolor(Belmarsh,34,FALSE)"
End Sub

"Belmarsh" is a cell range. Is there any way of inputting a cell specific
cell range i.e. E11:B12 or $E$11:$B$12 in VBA. Whenever I try this it errors.
Any ideas??

Thanks

Chris

 

[Tom Ogilvy]

Range("G4").Formula = "=countbycolor(E11:B12,34,FALSE)"

should work. Notice since the formula is in A1 notation, i use Formula
rather than FormulaR1C1

 

[Frank Kabel]

Hi
with activesheet.range("B11:E12")
..Interior.ColorIndex = 34
end with

 

[Don Guillett]

try
ActiveCell.Interior.ColorIndex = 34
Range("G4").Formula= "=countbycolor(range("e11:b12"),34,FALSE)"

 

[Don Guillett]

wrong
Range("G4").Formula= "=countbycolor(range("e11:b12"),34,FALSE)"

right
Range("G4").Formula= "=countbycolor(e11:b12,34,FALSE)"

 

[Tom Ogilvy]

Maybe you mean something like

Range("G4").Formula= "=countbycolor(" & _
range("e11:b12").Address & ",34,FALSE)"