Cell change causes action

  • Thread starter Thread starter keyser_Soze
  • Start date Start date
K

keyser_Soze

How can I fill a cell automatically when a different cell is changed?
For example, D2 contains a rate. When I enter data in any cell in
(d6:d19) I would the adjacent cell in (e6:e19) to be the value of the
cell in d*d2 (Same thing for range f6:f19 and g6:g19)

Thanks.
 
You can use the Change procedure of the worksheet. Right-click
the sheet tab, choose View Code, and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("D6:D19")) Is Nothing
Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
Target(1, 2) = Target * Range("D2")
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Just add formula in say E6

=IF(D6="","",$D6*$D$2)

and copy over.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
I can determine if the change was in the correct range. I do not know
how to verify the contents are numeric, and then adjust the value in
the adjacent cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Range("d6:d19")) Is Nothing
Then
MsgBox ("Change in D6:D19 range")
ElseIf Not Intersect(Range(Target.Address), Range("f6:f19")) Is
Nothing Then
MsgBox ("Change in F6:F19 range")
End If
End Sub
 
I wanted to do this via macro since the "E6:E19" range's values can
also be changed without using the value*rate method.

Thanks.
 
Back
Top