change fill color of range and back

[wene]

i've got the following snippet, which iterates trough a range and changes for all cell's without a fill color, the fill-color to white. How can I remember which cells were changed, so that I can change them back later

        ' change for cells without a fill-color the fill-color to white
        Dim cell As Range
        For Each cell In Selection
            If cell.Interior.ColorIndex = xlColorIndexNone Then
                On Error Resume Next
                cell.Interior.ColorIndex = 2
                cell.Interior.Pattern = xlSolid
            End If
        Next cell

thanks! (already in advance )

 

[wene]

better formulated:

how to store the adresses of several cells and how to read them out.

I assume a Array is best:

Dim cell As Range
        Dim CellArray() As String
        Dim ArraySize As Integer
        ArraySize = 0
        For Each cell In Selection
            If cell.Interior.ColorIndex = xlColorIndexNone Then
                ArraySize = ArraySize + 1
                CellArray(ArraySize) = cell.Address
                On Error Resume Next
                cell.Interior.ColorIndex = 2
                cell.Interior.Pattern = xlSolid
            End If
        Next cell

(...)

Dim iterations As Integer
        iterations = 0
        While iterations < ArraySize
            MsgBox (iterations)
            iterations = iterations + 1
            cell(CellArray(ArraySize)).Select
            cell.Interior.ColorIndex = 7
        Wend

Where are the bugs???

 

[mcgew]

How about adding white text when you change the cell; so the marking is invisible and you can easily identify further on in the macro or by changing cell properties at the worksheet.
Eg:
Dim cell As Range
For Each cell In Selection
If cell.Interior.ColorIndex = xlColorIndexNone Then
On Error Resume Next
cell.Interior.ColorIndex = 2
cell.Interior.Pattern = xlSolid
cell.Value = "changed"
cell.Font.Color = RGB(255, 255, 255)
End If
Next cell

If you don't want to enter anything into the cells, the other thing you could
do is create an array of the locations.
Eg:

Dim cell As Range, myarray() As Integer

For Each cell In Selection
ReDim myarray(mycount, 2)
If cell.Interior.ColorIndex = xlColorIndexNone Then
On Error Resume Next
cell.Interior.ColorIndex = 2
cell.Interior.Pattern = xlSolid
myrow = cell.Row
mycol = cell.Column
myarray(mycount, 1) = cell.Row
myarray(mycount, 2) = cell.Column
mycount = mycount + 1

End If
Next cell

End Sub

hope that helps

 

[wene]

coool,

did it not as beautyfull, but I believe my code is proper as well:

        'change for cells without a fill-color the fill-color to white and stores the address of each cell where fill-color was changed in an Array
        Dim cell As Range
        Dim CellArray() As String
        Dim ArraySize As Integer
        ArraySize = 0
        For Each cell In Selection
            If cell.Interior.ColorIndex = -4142 Then
                ArraySize = ArraySize + 1
                ReDim Preserve CellArray(ArraySize)
                CellArray(ArraySize) = cell.Address
                On Error Resume Next
                cell.Interior.ColorIndex = 2
                cell.Interior.Pattern = xlSolid
            End If
        Next cell

(...)

        'change the fill color back from white to none, based on the cell address in the Array
        Dim iterations As Integer
        iterations = 0
        While iterations < ArraySize
            iterations = iterations + 1
            Range(CellArray(iterations)).Select
            Selection.Interior.ColorIndex = -4142
        Wend