Workbook Calculate Event with Mee

[HJA14]

Dear All,

I would like to get some feedback how to write my desired module.

]I have a file in which column "O2:O50" is changing as new data is collected from an external source (Bloomberg). The same applies for "AD2:AD50" and "AS2:AS50". Now I would like to get a message if one of the values within these ranges is larger than the product of two cells who are always the same $A$1 and $A$2. In addition, I have multiple sheets, so I would like to make sure that the module applies for every sheet.

Sub Scanner() 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
With Sh
    If Range("O1:O50") or Range("AD2:AD50") or Range("AD2:AD50") > 0.1 * $B$3 * $B$5 Then
    Msgbox

But how do I make sure that the messagebox gives me the correct message? So the right range (of the three ranges) that satisfies the criteria? Ideally, I would like to get a message to say:

"In "...", the following option serie "..." satisfies the criteria as the volume, "..." has changed

where the first "..." represents the name of the sheet
the second "..." represents the cell 3 columns to the left of the cell in the range (that satisfies the criteria)
the third "..." is the value in the cell in the range (that satisfies the criteria)

Help is very much appreciated.

 

[NotoriousPopol]

Hi, using conditional format isn't enough? If not, something like :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Intersect(Target, Sh.Range("O1:O50")) Is Nothing Or _
    Not Intersect(Target, Sh.Range("AD2:AD50")) Is Nothing Or _
    Not Intersect(Target, Sh.Range("AS2:AS50")) Is Nothing Then
    
        MsgBox ("In " & Sh.Name & ", the following option serie " & Target.Offset(0, -3).Value & " satisfies the criteria as the volume " & Target.Value & " has changed")
End If

End Sub

Put it in the Workbook section.

 

[HJA14]

Thanks for your reply! Could you please explain me where the criteria (> 0.1 * $B$3 * $B$5) is located in your code? And could you give your opinion about the following code:

Sub Scanner()


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
	With Sh
		Dim r1, r2, r3, r4, r5, r6, my MultipleRange as Range
		Set r1 = Sh.Range("N1:N50")
		Set r2 = Sh.Range("AA1:AA50")
		Set r3 = Sh.Range("AN1:AN50")
		Set r4 = Sh.Range("BA1:BA50")
		Set r5 = Sh.Range("BN1:BN50")
		Set r6 = Sh.Range("CA1:CA50")
		Set myMultipleRange = Union(r1, r2, r3, r4, r5, r6)
		
		If Not Intersect(Target, Sh.r1) Is Nothing Or Not Intersect(Target, Sh.r2) Is Nothing Or No Intersect(Target, Sh.r3) Is Nothing Or Not Intersect(Target, Sh.r4)  Is Nothing Or Not Intersect(Target, Sh.r5) Is Nothing Or Not Intersect (Target, Sh.r5)
		Then
		MsgBox ("In " & Sh.Name & ", the following option serie " & Target.Offset(0, -3).Value & " satisfies the criteria as the volume " & Target.Value & " has changed")
End If
End Sub

 

[NotoriousPopol]

OUps! I forgot that part^^

If Target.Value > (0.1 * sh.Range("B3").Value * sh.Range("B5").Value ) Then
MsgBox ("In " & Sh.Name & ", the following option serie " & Target.Offset(0, -3).Value & " satisfies the criteria as the volume " & Target.Value & " has changed")

End If

Regarding your code, maybe you want to write :

If Not Intersect(Target, myMultipleRange) Is Nothing Then

? Don't know if that works tho.