Defining cells and ranges using variables
After Set rng=Range("A1")
rng(9,39) refers to AM9 and
Range(rng(9,39),rng(9,40)) refers to AM9:AN9
Target.Address is a string; the other side of the statement is a range.
Alan Beban
RogerPB wrote:
On Wed, 16 Mar 2005 11:08:47 -0800, Alan Beban
wrote:
One way to avoid the problems with the dots is to use
Set rng=Worksheets("Sheet3").Range("A1")
Then Range(rng(25,25),rng(27,27)) will refer to Y25:AA27 on Sheet3
In fact, I am running the macro whilst in sheet 3......
Anyway, I modified my routine as follows:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim mc As Integer
'If Target.Address = "$I$39" Then.... was the orignal line
'and was replaced by the lines proposed by Alan above.
Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then
'Worksheets("Sheet3").Range(Cells(1, 1), Cells(9, 9))
mc = Target.Value
Set rng = Worksheets("Sheet3").Range(Cells(25, 25), Cells(27, 27))
For Each cell In rng
rng.Interior.ColorIndex = mc
cell.Value = Target.Value
'Exit For
'End If
Next
End If
End Sub
However, replacing the line
'If Target.Address = "$I$39" Then...
by
Set rng = Worksheets("Sheet3").Range("A1")
If Target.Address = Range(rng(9, 39)) Then
led to the error message "Method 'Range' of object '-Worksheet'
failed.
So I am still looking for an expression identifying I39 by two
variables representing the row and the column
Rogerpb
|