Increase Cell Value By 1 If Another Cell Value =

[Lewzerrrr]

Hi,

I've searched around for this but can't come to a solid answer.. know it's probably very simple.

I need a code that when the cell in S4 changes to "27" then +1 in S3.

Never mind, figured it out lol

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("S4")) Is Nothing Then Exit Sub


If Range("S4").Value = "27" Then
    Range("S3").Value = Range("S3").Value + 1
End If


End Sub

Any help would be appreciated,

Thanks,

 

[JackDanIce]

It maybe you need the Calculate event, try:

Private Sub Worksheet_Calculate

If Range("S4").Value = 27 Then Range("S3").Value = .Range("S3").Value + 1

End Sub

OR in your original code, try changing "27" to 27, i.e. a numerical value rather than a string.

 

[Lewzerrrr]

It maybe you need the Calculate event, try:

Private Sub Worksheet_Calculate

If Range("S4").Value = 27 Then Range("S3").Value = .Range("S3").Value + 1

End Sub

OR in your original code, try changing "27" to 27, i.e. a numerical value rather than a string.

Thanks Jack, my code works yours is super simple, will remember for future ref

 

[JackDanIce]

You're welcome, but yours works because..? Ah missed the part

Never mind, figured it out lol

Mine's not "simpler", only that it's slightly different for a Worksheet_Calculate event than Worksheet_Change and for an IF statement in VBA, you can exclude End If Part, only if there's a single statement after THEN, e.g.

If Range("A1") = 1 Then
  Range("A2") = 2
End If

Can be written as:

If Range("A1") = 1 Then Range("A2") = 2

However:

If Range("A1") = 1 Then
  Range("A2") = 2
  Sheets(Range("A2")).Select
End If

Can't be reduced to a single line of code