macro to delete certain cells in a sheet

Joke said:

Hi,

Im having a sheet with a number of cells to enter data which are spread all over the page. I included an erase'button with macro to clear content of all cells where data is entered so users can use the same entry form again.
The sheet is protected and users can only enter de cells where data have to be entered.

The clumsy thing is that everytime we want to change the format of the sheet including the position of the datacells, I have to redo the entire macro because references in the macro are to each dataentry cell.

Is there a way to make a macro to select only the data entry cells on basis of the format (color) and then clear the content of these cells. I have tried to name the cells but then I have to include about 40 different names which is also rather clumsy.

Any tips or suggestions?

Thanks,

Joke

Click to expand...

Is there a way to make a macro to select only the data entry cells on basis of the format (color) and then clear the content of these cells.

Click to expand...

Sub erasecolorvalues()
    Dim rwIndex, colIndex As Integer

    For rwIndex = 1 To 27 ' rows 1 to 27
        For colIndex = 1 To 10 ' columns A to J
            With Worksheets("Sheet7").Cells(rwIndex, colIndex)
' colorindex for green is either 4 or 35 depending on which green
                If .Interior.ColorIndex = 4 Then .ClearContents
            End With
        Next colIndex
    Next rwIndex
End Sub

Joke said:

Excellent, the namng solution works very well!! only that the number of ranges you can add under one name has a limit. When it becomes to much the selected ranges just diseappear. So I had to use three names to cover all the cells.

I still have to try the macro but would you know by chance where can i find information on the numbers of all the colors.

In addition to the erasing I also have to fill a couple of cells with 0%. I tried naming them but then the macro only put 0% in the first cell. So apparently I should name them all individual to include them in the macro.

Joke

Click to expand...

...the number of ranges you can add under one name has a limit. When it becomes to much the selected ranges just diseappear. So I had to use three names to cover all the cells.

Click to expand...

Sub macro4()
     Range("MyMaster").ClearContents
End Sub

……where can I find information on the numbers of all the colors.

Click to expand...

Sub colorCode()
 c = 1
 For i = 1 To 8
  For j = 1 To 7
  Cells(i, j).Select
    With Selection.Interior
     .ColorIndex = c
    ActiveCell.Value = c
    End With
    c = c + 1
  Next j
 Next i
End Sub