Change cell value based on active cell

**kmar** · 01-28-2013, 10:10 AM

I have 2 columns G and I n which on each row a list exist to choose a value.
I would like that when I choose No in my list box of column G that I is changed into value 6.
And of course this would happen on each row where I change the value of column I.
I'm completely lost in the dark of macro writing unfortunately.
any help is welcome.

**OllieB** · 01-28-2013, 10:27 AM

What you need is a bit of VBA code that is automatically triggered as soon as you make a change to the specific worksheet. Excel supports this via events which can be monitored at workbook or specific worksheet level. You will need to copy & paste the code provided below into the module that is associated to the worksheet you are working with

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
     If Target.Column = 7 Then
          If Target.Value = "No" Then
               ActiveSheet.Cells(Target.Row, "I").Value = 6
          End If
     End If
End Sub

**kmar** · 01-29-2013, 02:33 AM

Thanks for the reply.
Just a question, why is target.column a number value and in target row a letter value?
My first thought would be the opposite no?

**OllieB** · 01-29-2013, 02:36 AM

Target is a range object and has many properties amongst which are value, row, column etc

**OllieB** · 01-29-2013, 02:56 AM

The letter you refer to is a parameter of the Cells object and not the Target object. The instruction means a cell on the same row as the target cell in column I