Update cell value depending another cell values.

[Newbie212]

Hello Excell masters,
Im trying to achieve the following thing:

I have a macro(button) that pushes information from cell B6 to hidden list in Q collumn, where the curently populated row in the list is selected from a List Box choice. The list box has 10 choices/rows, and the cell link is cell O6, getting values from 1 to 10 depending on the choice.

What im trying to do is get the reverse, - everytime when i press row 5(or any other) on the list box, i need the value of B6 to update to the value of the hidden list on row 5 be it value or "".

I have several variations of this "idea" but i cant get it to work, and im not sure how to isolate it to track changes/calculations only in O6.

Private Sub Worksheet_Calculate()
If Range("o6").Value = 1 Then
      Range("b6").Value = Range("q1").Value
      
ElseIf Range("o6").Value = 2 Then
      Range("b6").Value = Range("q2").Value
      
ElseIf Range("o6").Value = 3 Then
      Range("b6").Value = Range("q3").Value
      
ElseIf Range("o6").Value = 4 Then
      Range("b6").Value = Range("q4").Value
      
ElseIf Range("o6").Value = 5 Then
      Range("b6").Value = Range("q5").Value
        
End If

End Sub

I will be grateful if someone can help out. Thanks in advance.

 

[Legacy 143009]

I didn't understand your scenario fully. But If you only want to track the changes made in column O then you can try:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("O:O")) Is Nothing Then
   'Do your stuff
  End If
End Sub

If you want to track the changes made specifically in O6 then:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("O6")) Is Nothing Then
   'Do your stuff
  End If
End Sub