Right click on the tab that have B2:G2 that you want to trigger the code and past this code there. Any time the value of B2:G2 on that sheet changes value the code should run.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:G2")) Is Nothing Then
Application.EnableEvents = False
Dim cell As Range
Dim mycount As Long
Dim mycountplus As Long
'I assume you want to clear W22 if not you can remove the line below.
Range("V21").ClearContents
For Each cell In Range("V14:Z14")
If cell.DisplayFormat.Interior.Color = 12611584 Then
mycount = mycount + 1
End If
Next cell
'MsgBox (mycount)
If Range("AA14").DisplayFormat.Interior.Color = 12611584 And mycount = 0 Then
mycountplus = mycountplus + 1
MsgBox ("4")
Else
If Range("AA14").DisplayFormat.Interior.Color <> 12611584 And mycount >= 1 Then
End If
If Range("AA14").DisplayFormat.Interior.Color = 12611584 And mycount >= 1 Then ' Then
mycountplus = mycountplus + 1
mycount = mycount + 1
End If
'MsgBox ("mycountplus is" & mycountplus)
Select Case mycount
Case 0
MsgBox ("0")
Case 1
MsgBox ("10")
Case 1 + mycountplus
MsgBox ("14")
Case 2
MsgBox ("20")
Case 2 + mycountplus
MsgBox ("30")
Case 3
MsgBox ("40")
Case 3 + mycountplus
MsgBox ("50")
Case 4
MsgBox ("100")
Case 4 + mycountplus
MsgBox ("200")
Case 5
MsgBox ("300")
Case 5 + mycountplus
MsgBox ("500")
End Select
End If
End If
Application.EnableEvents = True
End Sub
Are you talking about Sheet1 tab and the Worksheet dropdown?
Already have this inside the following code in this event!
Private Sub Worksheet_Change(ByVal Target As Range)
'Sorts blank rows to the bottom as they occur
'Prevents endless loops
Application.EnableEvents = False
'They have more than one cell selected
If Target.Cells.Count >= 1 Then Exit Sub
If Target.Column <> 7 Then Exit Sub
If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then
Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
Application.EnableEvents = True
End Sub