Remember, SelectCell's address = B1. At this time Columns!$B$1 = 5 and
ColumnList!$B$1 = "W1:AF1". I want to set the interior color index of
Columns!W1:AF1 to gold (44).

"JLGWhiz" wrote:

You're a good man Jim Cone, I sure couldn't decipher it, but the Value part
didn't make sense to me either. I was also wondering if there shouldn't be a
comma after the Worksheets("ColumnList") clause. I just couldn't see where a
range was defined.

"Jim Cone" wrote:


"SelectCell.Adddress is set to B1 and its value is set to 5"

So this...
Worksheets("Columns").Range(Worksheets("ColumnList ") _
.Range(SelectCell.Address).Value).Interior.ColorIn dex = "clrGold"

Equals this ?...
Worksheets("Columns").Range(5).Interior.ColorIndex = "clrGold"

Which sheet is doing what to whom is difficult to determine,
but it appears you can remove ".Value" and it should work.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jerry McNabb"
wrote in message
The following macro is intended to accept, through two InputBoxes, a cell
address and a value to be entered into that cell. It then sets the interior
color to gold. 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 first worksheet.
It then does the same with a second crossreference and the second worksheet
and again with the third.

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.Address).Va lue = _
Application.InputBox(prompt:="Enter value", Type:=1)
Worksheets("Columns").Range(SelectCell.Address).In terior.ColorIndex =
clrGold
Worksheets("Columns").Range(Worksheets("ColumnList ") _
.Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold
Worksheets("Rows").Range(SelectCell.Address).Inter ior.ColorIndex =
clrGold
Worksheets("Rows").Range(Worksheets("RowList").Ran ge(SelectCell.Address) _
.Value).Interior.ColorIndex = clrGold
Worksheets("Rows").Range(SelectRange).Interior.Col or = clrGold
Worksheets("Areas").Range(SelectCell.Address).Inte rior.ColorIndex = clrGold

Worksheets("Areas").Range(Worksheets("AreaList").R ange(SelectCell.Address) _
.Value).Interior.ColorIndex = clrGold
End Sub

When I try to run it. Everything seems fine. clrGold is set to 44.
SelectCell.Adddress is set to B1 and its value is set to 5. Columns!$B$1 gets
set to 5 and its color is set to gold, just as expected. ColumnList!$B$1
contains "W1:AF1". The instruction
Worksheets("Columns").Range(Worksheets("ColumnList ") _
.Range(SelectCell.Address).Value).Interior.ColorIn dex = clrGold
returns a "Run-Time Error '1004'
"Application-Defined or Object-Defined Error".
Can someone help me?
Thank you.