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

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

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 said:
[c13].Value = Target
Click to expand...

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
Click to expand...


..
 
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)




Michael said:
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 said:
[c13].Value = Target
Click to expand...

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
Click to expand...


.
Click to expand...
 
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)




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 said:
[c13].Value = Target
Click to expand...

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
Click to expand...


.
Click to expand...


.
Click to expand...
 
Back
Top