Commandbutton loses transparency

Originally Posted by freeofcost

I am going to spend time on understanding how the code is getting executed and then going to come up with a different scenario and do it myself. I will come back to you if I need help. Thank you so much.

What I understood so far is that the application.caller with an offset detects the cell and puts a number '1' in it and then the sum function is used to calculate how many cells contain number '1' and match with the condition in cell G2. Wow! thats a wonderful logic (I hope I have understood correctly)

Now if I move Shapes or put the buttons horizontally, the code stops working.... I guess because of SET offsets and the range Range("B2:B8"). I have changed the scenario a bit and altered the code. however, it does not work and only one column is getting selected. I think I have not understood the TopLeftCell property well.

Can you please have a look at the code? I have updated the attached file with the instructions. There are two sheets in it "Working" and "Not Working"

Thank you

Sub ertertNotWorking()
With ActiveSheet.Shapes(Application.Caller).TopLeftCell.Offset(-2, 0)
    If .Interior.Color = vbGreen Then
        .Interior.Color = xlNone: .Offset(-1, 0).ClearContents
    Else
        If Range("F2").Value = "Yes" Then
            With Range("B3:D5")
                If WorksheetFunction.Sum(.Value) >= Range("G2").Value Then _
                   .Interior.Color = xlNone: .Offset(-1, 0).ClearContents
            End With
            .Interior.Color = vbGreen: .Offset(-1, 0).Value = 1
        End If
    End If
End With
End Sub