Help about an automation i want to create

[damav78]

Hi, I would like your help in an issue that i cannot find a way to solve.
I have 2 cells.
The first one has a dropdown list with one option only available.
The second one is a formula cell that collects multiple data from other cells and its dynamic. It means that when i change values to other cells this cell is affected.
So what i would like to do is:
When the first cell is blank the values on second cell to change as normally do (dynamically).
If from the first cell i choose the predefined value from my dropdown list then the second cell to actually "lock" the current value that has at that moment and dosen't be affected anymore from any changes.
Thank you in advance

 

[damav78]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 7 Or Target.Column <> 2 Then Exit Sub
    If Target <> "" Then
        Range("R" & Target.Row).Value = Range("R" & Target.Row).Value
        Range("T" & Target.Row).Value = Round(Range("T" & Target.Row).Value, 4)
    Else
        Range("R" & Target.Row).Formula = "=ROUND((A" & Target.Row & "*$D$1),0)"
        Range("T" & Target.Row).Formula = "=(L" & Target.Row & "-($D$3*$D$2))/$D$1"
    End If
End Sub

Still round up column T in 2 demicals

 

[damav78]

@mumps So did you find why it’s doing that? Is it anything I did wrong? On your code or the formula I have in the T column?

 

[damav78]

Dear @mumps after some testings i made with your previous code (post #26) i found out that the issue happens because of the cell format in column T. By default i have set it as currency cell, so with this format when i choose the list from the drop down it changes the value. If i format this cell as a number it doesn't do it. It keeps the value as it should.

 

[mumps]

So is everything OK now?

 

[damav78]

So is everything OK now?

Yes if I convert them as numbers. Is there an way to make it work right as currency cell?

 

[mumps]

If I leave the format as currency, the values in column T remain rounded to 4 decimals not 2.

 

[damav78]

If I leave the format as currency, the values in column T remain rounded to 4 decimals not 2.

Yes. The demicals are 4 but if you try it you will see that it makes a rounding in the first 2. As i wrote to you an example is before "locking" the value is 1,5512 and when you "lock" it, it makes it 1,5500
Check the first row from the file i uploaded. Just dont forget to move to another cell after locking

 

[mumps]

I used row 8 as a test. The value in T8 is 1.5665. I selected "Fixed" in B8 and the value in T8 remained the same as 1.5665. It did not round to 1.5600 or to 1.5700.

 

[damav78]

I used row 8 as a test. The value in T8 is 1.5665. I selected "Fixed" in B8 and the value in T8 remained the same as 1.5665. It did not round to 1.5600 or to 1.5700.

In my PC it makes it 1,5800 but the value was 1,5755. Also i noticed that i use , for demicals and not . as you do. Is there any way that where the problem is?

 

[mumps]

I have no way of testing it with a comma because in Canada the default is the dot.