can the following be put in an array

  • Thread starter Thread starter Aneurin
  • Start date Start date
A

Aneurin

hi the following code that goes through a range and see if
the cell 7 cells from the active cell is > 4 and if it is
it shade the cells in the row green
what i wanted to know can i put all the rng offsets in an
array instead of doing it how i have
this is what i have
Sub fill_it()

Dim rng As Range

For Each rng In Range("test")

If rng.Offset(0, 7).Value >= 4 Then
rng.Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 1).Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 2).Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 3).Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 4).Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 5).Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 6).Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 7).Select
Selection.Interior.ColorIndex = 4
rng.Offset(0, 8).Select
Selection.Interior.ColorIndex = 4
end if
next rng
end sub

thanks aneurin
 
Not an array, but how about:

Sub fill_it()

Dim rng As Range

For Each rng In Range("test")
If rng.Offset(0, 7).Value >= 4 Then
rng.Resize(1, 9).Interior.ColorIndex = 4
End If
Next rng

End Sub
 
Back
Top