Excel vba increase cell value without creating circular reference

Question

I have a spreadsheet in which cells D4:D93 are Data Validation drop down lists with only Yesand No as options.

I am trying to figure out how to code VBA so that when Yes is selected in a cell in that range, then the value in the coinciding cell in range I4:I93 changes to be the sum of the coinciding cells in I4:I93 and V4:V93.

I've been searching for days and tried so many things I don't even know where to begin as far as what I've tried. Some attempts resulted in nothing happening. Other resulted in Excel freezing or crashing due to the circular references. Don't know if this is relevant, but D4:D93 and I4:I93 are part of a table but V4:V93 is not.

Gary's Student Gary's Student · Accepted Answer · 2016-02-29T02:14:19

Try this worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim D As Range, rw As Long
    Set D = Range("D4:D93")

    If Intersect(Target, D) Is Nothing Then Exit Sub
    If LCase(Target.Value) <> "yes" Then Exit Sub
    rw = Target.Row
    Application.EnableEvents = False
        Range("I" & rw).Value = Range("I" & rw).Value + Range("V" & rw).Value
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Excel vba increase cell value without creating circular reference

1 Answers