Setting Numberformat in If statement

[ArnMan]

Hello, I have a quick question. I have 2 cells I would like to use if elseif to set the numberformat in one cell based on a value in another cell. Here is what I have so far, which isnt doing anything for me.

If Range("J5").Value = "0" Then
      Range("LowCB2").NumberFormat = "0"
      Range("LowCB2").Value = "=D9-M9"
     ElseIf Range("J5").Value = "1" Then
        Range("LowCB2").NumberFormat = "0.0"
        Range("LowCB2").Value = "=D9-M9"
     ElseIf Range("J5").Value = "2" Then
        Range("LowCB2").NumberFormat = "0.00"
        Range("LowCB2").Value = "=D9-M9"
     ElseIf Range("J5").Value = "3" Then
        Range("LowCB2").NumberFormat = "0.000"
        Range("LowCB2").Value = "=D9-M9"
        
         End If

If I just put
LowCB2.Numberformat = "0.00"
LowCB2.Value = "=D9-M9"

in with the rest of the code it works, but if I try to use the if statement neither one of those LowCB2's show up.

Any help would be greatly appreciated

 

[ArnMan]

Hello, Yes I apologize for that this is the first time I've used VBA and this is the first question I've had to ask. So I wasnt sure what all I needed.

I have been able to get both of ya'lls code and my code to work, now. Only it doesnt automatically update when a new number is changed. I can put a number lets say "2" in I9 and it wont update J5. Until I reaccess the Sub, leaving the 2 in place. I'll eventually have the Sub clear all formatting and text every time it is accessed, in a certain area, which contains I9 and J5. I have 2 subs that run in the small selected area. the Other one clears everything. And when I access the Sub with LowCB2, it is reset, and I guess it fired once during execution. And when I actually put a number in there it doesnt fire again,.... I hope I am making sense.
I read somewhere run a function on Change, but I am not sure how to do that, if that is what I need to do.

I agree with Dave... it would have been nice had you shared this with us originally. What you wrote originally, Range("LowCB2"), would have been correct if you had assigned a Defined Name from Excel's Ribbon (Formula tab, Defined Names panel) to a range on the worksheet, so there was no way for us to know your original attempt at writing the range reference was not correct for what you were doing. Anyway, here is my code snippet modified to properly define LowCB2...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dim LowCB2 As Range
Set LowCB2 = Sheet1.Range("I9")
LowCB2.NumberFormat = "0" & IIf(Range("J5").Value, ".", "") & String(Range("J5").Value, "0")
LowCB2.Formula = "=D9-M9"[/TD]
[/TR]
</tbody>[/TABLE]

 

[ArnMan]

Hello
I believe I have found a way to get it to work I had a worksheet change for a in cell drop down that I had created so I added if statement inside of that. its not pretty I dont know all the shortcuts but it is working for me now I'll try to one of ya'lls in it instead your code looks neater
but here is what I have that is working

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LowCB2 As Range
Set LowCB2 = Sheet1.Range("I9")


If Not Intersect(Target, Range("B2")) Is Nothing Then
    Select Case Range("B2")
        Case "Normal Test Point": ChangeRowType
        Case "Blank Line": ChangeRowTypeBlank
    End Select
End If

If Range("J5") = "0" Then
LowCB2.NumberFormat = "0"
LowCB2.Value = "=D9-M9"
End If
If Range("J5") = "1" Then
LowCB2.NumberFormat = "0.0"
LowCB2.Value = "=D9-M9"
End If
If Range("J5") = "2" Then
LowCB2.NumberFormat = "0.00"
LowCB2.Value = "=D9-M9"
End If
If Range("J5") = "3" Then
LowCB2.NumberFormat = "0.000"
LowCB2.Value = "=D9-M9"
End If
If Range("J5") = "4" Then
LowCB2.NumberFormat = "0.0000"
LowCB2.Value = "=D9-M9"
End If
If Range("J5") = "5" Then
LowCB2.NumberFormat = "0.00000"
LowCB2.Value = "=D9-M9"
End If
If Range("J5") = "6" Then
LowCB2.NumberFormat = "0.000000"
LowCB2.Value = "=D9-M9"
End If
End Sub

 

[ArnMan]

Hello I think I have figured out how to do it. the last code I shown must have created a loop or something and crashes my excel when I add a second set
so I have updated the code to show this and that code calls some modules, still new to modules and well everything vba related

I want to thank you for showing me the right direction to take
Thank you Im sure i'll have many more questions as I go along

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Select Case Range("B2")
        Case "Normal Test Point": ChangeRowType
        Case "Blank Line": ChangeRowTypeBlank
    End Select
End If

If Target.Address = "$J$5" Then
Call LCB2
Call HCB2
End If
End Sub