change fill color of range and back

wene

New Member
Joined
Dec 15, 2004
Messages
47
(y)

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 :unsure:

Code:
        ' 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 :rolleyes: )
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

wene

New Member
Joined
Dec 15, 2004
Messages
47
better formulated:

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

I assume a Array is best:

Code:
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

Board Regular
Joined
Sep 4, 2002
Messages
75
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

New Member
Joined
Dec 15, 2004
Messages
47
coool,

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

Code:
        '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

(...)


Code:
        '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
 

Forum statistics

Threads
1,147,696
Messages
5,742,679
Members
423,747
Latest member
Shadeslayers09

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top