Hi,

I have the following macro which is not working completely as
required.

I want the macro to look into a range C18 to C45 and push the content
of the cell into C13. Incase any other cell is clicked, the macro
should not perform any action.

However the below macro selects all the cells on the sheet when click
instead of the cells within the C18:C45 range.

Can anyone advise improvement to the below macro

Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("C18:C45")
For Each cell In rng
Range("c13").Value = ActiveCell.Value
Next
End Sub

You need to check against target. If you copy more than one cell into the
range c18:c45 which value do you want to be put into c13? Should c13 be more
than one location?

Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In target
if application.intersect(cell,Range("C18:C45")) then
Range("c13").Value = cell.Value
end if
Next cell
End Sub

"Sandip" wrote:

> Hi,
>
> I have the following macro which is not working completely as
> required.
>
> I want the macro to look into a range C18 to C45 and push the content
> of the cell into C13. Incase any other cell is clicked, the macro
> should not perform any action.
>
> However the below macro selects all the cells on the sheet when click
> instead of the cells within the C18:C45 range.
>
> Can anyone advise improvement to the below macro
>
> Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim rng As Range
> Set rng = Range("C18:C45")
> For Each cell In rng
> Range("c13").Value = ActiveCell.Value
> Next
> End Sub
>

Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "c18:c45"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Range("c13").Value = Target.Value
End With
End If
End Sub


Gord Dibben MS Excel MVP

On Mon, 23 Mar 2009 06:10:37 -0700 (PDT), Sandip <(E-Mail Removed)>
wrote:

>Hi,
>
>I have the following macro which is not working completely as
>required.
>
>I want the macro to look into a range C18 to C45 and push the content
>of the cell into C13. Incase any other cell is clicked, the macro
>should not perform any action.
>
>However the below macro selects all the cells on the sheet when click
>instead of the cells within the C18:C45 range.
>
>Can anyone advise improvement to the below macro
>
>Sub Worksheet_SelectionChange(ByVal Target As Range)
>Dim rng As Range
>Set rng = Range("C18:C45")
> For Each cell In rng
> Range("c13").Value = ActiveCell.Value
> Next
>End Sub