Skip to content

Instantly share code, notes, and snippets.

@Qleoz12
Last active July 22, 2019 16:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Qleoz12/144f7cfc22c3b733b6d8a513e8f5b7f4 to your computer and use it in GitHub Desktop.
Save Qleoz12/144f7cfc22c3b733b6d8a513e8f5b7f4 to your computer and use it in GitHub Desktop.
VBA how call funtion on edit cell over a specific range
Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox Target.Count
'MsgBox Target.Value
'MsgBox Len(Target.Value)
'MsgBox InRange(Target, Range("A2:A1000"))
If InRange(Target, Range("A2:A1000")) And Target.Count = 1 Then
Call colorValidate(showMatch(Target.Value), Target)
Else
'MsgBox "NO SE EJECUTA"
End If
End Sub
Function InRange(Range1 As Range, Range2 As Range) As Boolean
'returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function
Function showMatch(ByVal text As String) As Boolean
Dim regex As Object, str As String
Set regex = CreateObject("VBScript.RegExp")
With regex
.Pattern = "^[3][0-9]{9}$"
End With
Debug.Print regex.Test(text)
showMatch = regex.Test(text)
End Function
Function colorValidate(ByVal resutl As Boolean, ByVal Target As Range)
If resutl Then
Target.Interior.Color = RGB(0, 255, 0)
Else
Target.Interior.Color = RGB(208, 73, 37)
MsgBox "VALOR Invalido"
End If
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment