Hi,
Help!
I have a macro fired from a Worksheet_Change event that I want to run when a cell in column H is set to a value of >= 0.9. The macro opens an InputBox and I want to put the user entered string (strReply) into column S on the same row.
I think I have it almost working, just struggling with 2 things:
1. As you see from the Worksheet_Change sub below I track changes to some columns between C and R and put date and time in column B on change and I still want to do that. But unfortunately all changes to these columns also run my Details macro but I only want the macro to run when column H is changed to >= 0.9 not when other columns are changed or when column H is lower than 0.9.
2. At the moment, if I change column H on any row the macro runs and puts strReply in cell S4 on my sheet called "Test" and I am strugling to work out how to make it put it in column S on the same row as the changed column H cell. Column H is a percentage probability and is manually updated to a value between 10% and 100% and I only want the Details at probabilites of 90% and above.
Private Sub Worksheet_Change(ByVal Target As Range)
Col = Left(Target.Address, 2)
If Intersect(Target, Range("C:R")) Is Nothing Then Exit Sub
If Col = "$C" Then Target.Offset(0, -1) = Now
If Col = "$D" Then Target.Offset(0, -2) = Now
If Col = "$E" Then Target.Offset(0, -3) = Now
If Col = "$G" Then Target.Offset(0, -5) = Now
If Col = "$H" Then Target.Offset(0, -6) = Now
If Col = "$J" Then Target.Offset(0, -8) = Now
If Col = "$K" Then Target.Offset(0, -9) = Now
If Col = "$L" Then Target.Offset(0, -10) = Now
If Col = "$M" Then Target.Offset(0, -11) = Now
If Col = "$N" Then Target.Offset(0, -12) = Now
If Col = "$O" Then Target.Offset(0, -13) = Now
If Col = "$P" Then Target.Offset(0, -14) = Now
If Col = "$Q" Then Target.Offset(0, -15) = Now
If Col = "$R" Then Target.Offset(0, -16) = Now
If Target.Value >= 0.9 Then
Details
End If
End Sub
And the macro:
Sub Details()
Dim i As Long
Dim LastRow As Long
Dim strReply As String
Dim strTitle As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
LastRow = Range("H" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "H").Value >= 0.9 Then
With objRegex
.ignorecase = True
Do
If strReply <> vbNullString Then strTitle = "Please retry"
strReply = Application.InputBox("Please enter details")
If strReply = "False" Then
MsgBox "You cancelled, please don't forget to put the details in later", vbCritical
Exit Sub
End If
Loop Until .test(strReply)
End With
Sheets("Test").[s4].Value = (strReply)
End If
Exit Sub
Next
End Sub
I am not quite a novice but and definitely an amateur.
Regards,
Geoff.
Bookmarks