CODEWITHSUNDEEP
excelvba
Mohammad HG
Mohammad HG

Reputation: 1

find the cells that reach to certain mount excel vba

there are multiple cells with numbers in a range. I want to mark the cells which sum of them will reach to a certain number. For example: this code will find just two cells, but I need at least 30 cells

For each cell1 in range("A1:A30")
  For each cell2 in range("A1:A30")
    If cell1.value+cell2.value= MyNumber then
      Cell1.interior.color=vbRed
      Cell2.interior.color=vbRed
      Exit sub
    End if
  Next cell2
Next cell1

Thanks for helping me.

Upvotes: 0

Views: 79

Answers (1)

Sachin Kohli
Sachin Kohli

Reputation: 1986

Try this; for range A1:A30, if cummulative sum reaches MyNumber, then will color red that range & exit the code...

Sub code()
MyNumber = 45
num = 0
For i = 1 To 30
    num = num + Range("A" & i).Value
    If num >= MyNumber Then
        Range("A1:A" & i).Interior.Color = vbRed
        Exit Sub
    End If
Next

End Sub

Upvotes: 0

Related Questions