Repost: Worksheet Change Method (Bob Philips, Ron De Bruin)
 

Hi guys, I don't think I explained very cleary what I am
trying to achieve. I would like a range of values in
column C to match the corresponing value in Column D when
the user enters a Value in a cell in Column D.

I can do it with one cell, for example

If Target.Address < "$D$13" Then Exit Sub
[c13].Value = Target

but how to you expand this to all of columns D & C ?

Thanks,

Michael


Michael,

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("$D$13:E100")) Is
Nothing Then
With Target.Offset(0, -1)
With .Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
.Font.Bold = True
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips

"Michael" wrote in message
...

Hi all,

Is it possible to expand the code below to apply it a
range of cells. At present it only works on a single cell
D13 and consequently C13.Is this possible?,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address < "$D$13" Then Exit Sub
[c13].Value = Target
Range("C13").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Selection.Font.Bold = True
End Sub

Thanks for your help

Michael


..

Repost: Worksheet Change Method (Bob Philips, Ron De Bruin)
 

Try this Michael

It is working for the whole column D

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Target.Offset(0, -1).Value = Target.Value
End If
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Michael" wrote in message ...

Hi guys, I don't think I explained very cleary what I am
trying to achieve. I would like a range of values in
column C to match the corresponing value in Column D when
the user enters a Value in a cell in Column D.

I can do it with one cell, for example

If Target.Address < "$D$13" Then Exit Sub
[c13].Value = Target

but how to you expand this to all of columns D & C ?

Thanks,

Michael


Michael,

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("$D$13:E100")) Is
Nothing Then
With Target.Offset(0, -1)
With .Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
.Font.Bold = True
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips

"Michael" wrote in message
...

Hi all,

Is it possible to expand the code below to apply it a
range of cells. At present it only works on a single cell
D13 and consequently C13.Is this possible?,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address < "$D$13" Then Exit Sub
[c13].Value = Target
Range("C13").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Selection.Font.Bold = True
End Sub

Thanks for your help

Michael


.

Repost: Worksheet Change Method (Bob Philips, Ron De Bruin)
 

Hi Ron, That works perfect !

Thanks,

Michael

-----Original Message-----
Try this Michael

It is working for the whole column D

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Target.Offset(0, -1).Value = Target.Value
End If
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Michael" wrote in message
...

Hi guys, I don't think I explained very cleary what I am
trying to achieve. I would like a range of values in
column C to match the corresponing value in Column D
when
the user enters a Value in a cell in Column D.

I can do it with one cell, for example

If Target.Address < "$D$13" Then Exit Sub
[c13].Value = Target

but how to you expand this to all of columns D & C ?

Thanks,

Michael


Michael,

Try

Private Sub Worksheet_Change(ByVal Target As
Excel.Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("$D$13:E100")) Is
Nothing Then
With Target.Offset(0, -1)
With .Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
.Font.Bold = True
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips

"Michael" wrote in message
...

Hi all,

Is it possible to expand the code below to apply it a
range of cells. At present it only works on a single
cell
D13 and consequently C13.Is this possible?,

Private Sub Worksheet_Change(ByVal Target As
Excel.Range)
If Target.Address < "$D$13" Then Exit Sub
[c13].Value = Target
Range("C13").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Selection.Font.Bold = True
End Sub

Thanks for your help

Michael


.




.