Running Loop in an Array

I currently use a lot of loops in my macros. Someone recently introduced me to arrays and they seem to be much faster at running through the data. However, when an action needs to be taken, it has to access the sheet again and this seems to be just as slow. Is there a way to run through the array and save all the necessary changes? Then execute all the changes in one step at the end? Here is the code.

Sub Test()

rowsend = Range("A" & Rows.Count).End(xlUp).Row

varsheet = Range(Cells(1, 1), Cells(rowsend, 1))

For i = 1 To rowsend

If varsheet(i, 1) = "I'm Awesome" Then
Cells(i, 1).Interior.Color = 1234545
End If

Next



End Sub

Your array is correct, but you can not use or test properties in arrays, hence you used.

Cells(i, 1).Interior.Color = 1234545

Arrays live in memory, but you are testing property of a range, not array, if you do, you will get an error.

Perhaps next code

Sub Test2()
    rowsend = Range("A" & Rows.Count).End(xlUp).Row
    varsheet = Range(Cells(1, 1), Cells(rowsend, 1))
    Dim WkRg  As Range
    For i = 1 To rowsend
        If varsheet(i, 1) = "I'm Awesome" Then
            If WkRg Is Nothing Then
                Set WkRg = Cells(i, 1)
            Else
                Set WkRg = Union(WkRg, Cells(i, 1))
            End If
        End If
    Next
    WkRg.Cells.Interior.Color = 1234545
End Sub

Your code could be improved to mix array and range.

Sub Test()

rowsend = Range("A" & Rows.Count).End(xlUp).Row

varsheet = Range(Cells(1, 1), Cells(rowsend, 1))

For i = 1 To UBound(varsheet)

If varsheet(i, 1) = "I'm Awesome" Then
Cells(i, 1).Interior.Color = 1234545
End If

Next


End Sub

PCI this seems to be my answer. Setting screenupdating is also a good suggestion. Thanks