On Saturday, July 8, 2017 at 9:39:06 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Sat, 8 Jul 2017 09:06:48 -0700 (PDT) schrieb L. Howard:

Ref the commas and semi-colons, apparently that is a problem with the users in columns B C D, which are addresses.

Values entered in column I & AE are percentages with the columns formatted as %.
I now have both columns and all the formulas correctly displaying and adding as percent.

So I use this in the code and seems to work.

If (Range("I20") / 1) 1 Then

The Calculate code does not respond, but the code in the sheet_change does.

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
Application.ScreenUpdating = False

If Not Intersect(Target, Range("B4:D18")) Is Nothing Then
If InStr(Target, ",") + InStr(Target, ";") 0 Then
Target = Replace(Replace(Target, ",", ""), ";", "")
End If
ElseIf Not Intersect(Target, Range("I4:I18,AE4:AE18")) Is Nothing Then
If Range("I20") 1 Then
MsgBox "Funds allocated among properties cannot be greater than
100%" & vbCr & _
"Review values in Columns I and AE"
End If
End If
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus, Garry,

I am using this which covers the bases on two different ranges for the commas/semi-colons and alerts when I20 is 100.

Private Sub Worksheet_Change(ByVal Target As Range)

If (Range("I20") / 1) 1 Then
MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _
"Review values in Columns I and AE"
End If

If Intersect(Target, Range("B4:D18,Z4:AB18")) Is Nothing Or Target.Cells.Count 1 Then Exit Sub

If InStr(1, Target, ",") 0 Or InStr(1, Target, ";") 0 Then

Application.EnableEvents = False

With Target
.Replace What:=",", Replacement:="", LookAt:=xlPart
.Replace What:=";", Replacement:="", LookAt:=xlPart
End With

Application.EnableEvents = True

Else

Application.EnableEvents = True
Exit Sub

End If

MsgBox "Comma's or Semi-Colon's removed from " & Target.Address(False, False)


End Sub