Run-time error '1004'
 

I have a macro which accepts, through two InputBoxes, a cell address and a
value to be entered into that cell. It then sets the interior color to gold
(44). It then looks into a cross reference matrix. Each cell in the matrix
contains a range of addresses in the format "L1:U1". It then sets the
interior colors of the ten cells in that range within the second worksheet.
It then does the same with a second crossreference and the third worksheet.

The code I have is:

Sub Initialize()
Dim clrGold As Integer
Dim SelectCell As Range

clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex
Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8)

Worksheets("Columns").Range(SelectCell).Value = _
Application.InputBox(prompt:="Enter value", Type:=1)
Worksheets("Columns").Range(SelectCell).Interior.C olorIndex = clrGold

Worksheets("Columns").Range(Worksheets("ColumnList ").Range(SelectCell).Value)
_
.Interior.ColorIndex = clrGold
Worksheets("Rows").Range(SelectCell).Interior.Colo rIndex = clrGold
Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell).Value) _
.Interior.ColorIndex = clrGold

Everything seems to work fine until I try to set the cell value I have
entered the range B1 then it displays the second input box and I enter 5. At
that point I get the "Run-time error '1004'
"Application-defined or object-defined error."
It does not set cell B1 to 5 or any other displayable value. It does not set
any cell to 5.

Can you please tell me the dumb mistake I am making?

Thanks.

Run-time error '1004'
 

Hi
The syntax Range(Address) assumes that Address is a string.
Range(SelectCell) will then cause an error.
In your line
Worksheets("Columns").Range(SelectCell).Interior.C olorIndex = clrGold

If SelectCell is on Worksheets("Columns") then the syntax
SelectCell.Interior.ColorIndex = clrGold

will do the job.
If SelectCell is not on Worksheets("Columns"), or it might not be, use
Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex =
clrGold

This second option is probably safer on the whole.

regards
Paul


On Feb 14, 5:41*am, Jerry McNabb
wrote:
I have a macro which accepts, through two InputBoxes, a cell address and a
value to be entered into that cell. It then sets the interior color to gold
(44). It then looks into a cross reference matrix. Each cell in the matrix
contains a range of addresses in the format "L1:U1". It then sets the
interior colors of the ten cells in that range within the second worksheet..
It then does the same with a second crossreference and the third worksheet..

The code I have is:

Sub Initialize()
* * Dim clrGold As Integer
* * Dim SelectCell As Range

* * clrGold = Worksheets("Columns").Cells(13, 2).Interior.ColorIndex
* * Set SelectCell = Application.InputBox(prompt:="Select a cell", Type:=8)

* * Worksheets("Columns").Range(SelectCell).Value = _
* * * * * Application.InputBox(prompt:="Enter value", Type:=1)
* * Worksheets("Columns").Range(SelectCell).Interior.C olorIndex = clrGold

Worksheets("Columns").Range(Worksheets("ColumnList ").Range(SelectCell).Valu*e)
_
* * * * * .Interior.ColorIndex = clrGold
* * Worksheets("Rows").Range(SelectCell).Interior.Colo rIndex = clrGold
* * Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell).Value) _
* * * * * .Interior.ColorIndex = clrGold

Everything seems to work fine until I try to set the cell value I have
entered the range B1 then it displays the second input box and I enter 5. At
that point I get the "Run-time error '1004'
"Application-defined or object-defined error."
It does not set cell B1 to 5 or any other displayable value. It does not set
any cell to 5.

Can you please tell me the dumb mistake I am making?

Thanks.