VBA G列セル列が空欄の場合、最終計算額の大小により判別するMセルの表示を変えたい。

作業シートを作成中です。次のコードで作業しています。ご指導頂いたコードなので感謝しています。
添付のシートのG35:G39の列に数字が入らない場合にG40＝G34（G40＝G34-(SUM（G35:G39))
となるが、M40の表示が変らない。（なお、G35:G39セルのどこか１セルに数字を入れた場合は表示が変ります。）
G35:G39セルに空欄のまま何も入れなくて、G40セルの数字の大小により表示される様にしたい。
添付画像のG40セルが2000なのでM40セルは「0」表示なのですが、「セルに指定数字を入力」が表示されたままで変りません。ご指導よろしくお願いします。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim g As Long
If Intersect(Target, Range("G35:G39")) Is Nothing Then Exit Sub
If Target(1).Value < 10000 Or Not IsNumeric(Target.Value) Then
'MsgBox ("M" & Target.Row & "セルに 「0」を入力")
Target.Offset(, 4).Value = 0
Target.Offset(, 4).Select
Else
'MsgBox ("M" & Target.Row & " セルに指定数字を入力")
Target.Offset(, 4).Value = "このセルに指定数字を入力"
Target.Offset(, 4).Select
End If

g = Range("G34").Value - Application.Sum(Range("G35:G39"))

If g < 10000 Then '未満の場合、以下ならｇ<＝10000
'MsgBox("M40" & Target.Row & "セルに「0」を入力")
Range("M40").Value = 0
Target.Offset(, 4).Select
Else
'MsgBox("M40" & Target.Row & "セルに指定数字を入力")
Range("M40").Value = "このセルに指定数字を入力"
Target.Offset(, 4).Select

End If

No.6 ベストアンサー 回答者： Qchan1962 回答日時： 2020/08/21 23:36

#5お礼について

＃３のご希望と一致しません。
今一度、精査してなさりたい事を纏めて頂けると良いと思います。
不一致箇所

①当初G40セルはG34に合計値を手入力後、同数値を表示します。
①はどのような事を指すのでしょう？

＞差引合計値セルはM40ではなくG40なのでG40に修正させて頂きました。
②G35～G3９に数値を手入力するとその合計値をG34セル値から引いた数値がM40に表示されます。

＞M40に10000以上の場合「このセルに指定数字を入力」と未満の場合に「０」の表示されませんでした。
そのような要件は、＃３の補足にはありません。なので、当初あったものを削除いたしました。

＃３で　使用要件、実行条件（付けたしで増えている仕様）を精査した方が良いと思います。
に対しての①②②③と理解したのですが、そうではなかったと言う事でしょうか、それとも打ち間違い？

いずれにしても、良いのですが、、
＞、M40に10000以上の場合「このセルに指定数字を入力」と未満の場合に「０」の表示されませんでした。
と言う事は、②G35～G3９に数値を手入力するとその合計値をG34セル値から引いた数値がM40に表示されます。は、実現できませんが
良いと言う事でしょうか？（M40に表示されるのは、このセルに　指定数字を入力　または　0　です。　引いた数値は表示されません。

良いと言う事であれば、Range("G40").Value = Range("G34") - Application.Sum(myRng)　を

if Range("G34") - Application.Sum(myRng) >= 10000 then
Range("G40").Value = "このセルに指定数字を入力"
else
Range("G40").Value =0
end if
とすればよいでしょう。が、、＃３と＃５を組み合わせて、、

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
　　Set myRng = Range("G35:G39")
　　If Intersect(Target, Range("G34", myRng)) Is Nothing Then Exit Sub
　　Application.EnableEvents = False
　　If Target.Address(0, 0) = "G34" Then
　　　　Range("G40").Value = Range("G34") - Application.Sum(myRng)
　　　　Call Val_Offset(Range("G40"), myRng)
　　　　GoTo Ext
　　End If
　　On Error GoTo DelEr
　　Call Val_Offset(Target, myRng)
　　Range("G40").Value = Range("G34") - Application.Sum(myRng)
　　Call Val_Offset(Range("G40"), myRng)
　　If Target(1).Value = "" Then Target.Offset(, 4) = ""
Ext:
　　Application.EnableEvents = True
　　Exit Sub
DelEr:
　　If Range("G40") = "" Then
　　　　Range("G39,M39") = ""
　　　　Range("G39:I39").Merge
　　　　Range("G40:I40").Merge
　　　　Range("G40:I40").Borders.LineStyle = True
　　　　Range("G40").Value = Range("G34") - Application.Sum(myRng)
　　End If
　　GoTo Ext
End Sub
Sub Val_Offset(Target As Range, myRng As Range)
　　With Target
　　　　If .Value < 10000 Or Not IsNumeric(.Value) Then
　　　　　　.Offset(, 4).Value = 0
　　　　Else
　　　　　　.Offset(, 4).Value = "このセルに指定数字を入力"
　　　　　　If Target.Address(0, 0) <> "G40" Then .Offset(, 4).Select
　　　　End If
　　End With
End Sub

この回答へのお礼

夜遅くまでご教授いただき申し訳御座いませんでした。
コードを試行しました。完璧に作動し満足のいく作業シートが完成しました。
私の相談に当初からご教授いただき感謝しています。有り難う御座いました。

お礼日時：2020/08/22 10:29

No.7 回答者： めぐみん_ 回答日時： 2020/08/22 05:47

なんかいっその事使用していないワークシートがあるなら、Calculateイベント（シートが再計算された時に発生します。

）と組み合わせた方が楽なのかな？
なんて初級者はアホな考えを持ってしまって『ど～しよ～もない』ですね。

この回答へのお礼

有り難う御座いました。

お礼日時：2020/08/22 10:18

No.5 回答者： Qchan1962 回答日時： 2020/08/21 18:51

＃３です。

考えすぎてしまったか、、悪い癖です。
理解していたのとだいぶ違うので、書き直しました。

①当初G40セルはG34に合計値を手入力後、同数値を表示します。
②M40はG35:G39が空欄でも実行します。
②G35～G3９に数値を手入力するとその合計値をG34セル値から引いた数値がM40に表示されます。
（G35～G39の数値により、M40の数値は変化します。）
③定数の10000は変更しません。　　　私の説明が不十分ですみません。よろしくお願いします。

以前あった削除に対してのコードを一応、足しました。（上に詰める場合のみ対応）

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim myRng As Range
　　Set myRng = Range("G35:G39")
　　If Intersect(Target, Range("G34", myRng)) Is Nothing Then Exit Sub
　　Application.EnableEvents = False
　　Range("G40").Value = Range("G34")
　　If Target(1).Value = "" Then
　　　　Target.Offset(, 4) = ""
　　　　GoTo Ext
　　End If
　　If Target.Address(0, 0) <> "G34" Then
　　　　With Target
　　　　　　On Error GoTo DelEr
　　　　　　If .Value < 10000 Or Not IsNumeric(.Value) Then
　　　　　　　　.Offset(, 4).Value = 0
　　　　　　Else
　　　　　　　　.Offset(, 4).Value = "このセルに指定数字を入力"
　　　　　　　　If Not Intersect(Target, myRng) Is Nothing Then .Offset(, 4).Select
　　　　　　End If
　　　　End With
　　Else
　　End If
Ext:
　　Range("M40").Value = Range("G34") - Application.Sum(myRng)
　　Application.EnableEvents = True
　　Exit Sub
DelEr:
　　If Range("G40") = Range("G39") Then
　　　　Range("G39") = ""
　　　　Range("M39") = ""
　　　　Range("G39:I39").Merge
　　　　Range("G40:I40").Merge
　　End If
　　GoTo Ext
End Sub

この回答へのお礼

いつも有り難う御座います。コードを試行しました。M40に「0」表示しましたので、差引合計値セルはM40ではなくG40なのでG40に修正させて頂きました。→Range("G40").Value = Range("G34") - Application.Sum(myRng)にして試行した結果、M40に10000以上の場合「このセルに指定数字を入力」と未満の場合に「０」の表示されませんでした。ご教授よろしくお願いします。

お礼日時：2020/08/21 20:46

No.4 回答者： めぐみん_ 回答日時： 2020/08/21 18:45

No.2です。

＞G40の数値が変化（基準10000以上、未満で表示が変る。）してもM4０セルの表示が変りませんでした。

Ｇ４０が変化した時とは具体的にどこのセルを弄ったのでしょうか？
或いは別のシートを参照して数式でＧ３４が変化した時ではないですよね？

この回答へのお礼

有り難う御座います。②G34は手入力でその日のデータを合計した数値で、G35～G39セルにG34の範囲にで数値を入力し、①その合計値をG34から差引した数値がG40で変化し、10000を超えた場合と未満に判定した結果がM40に表示される様にしたい。　よろしくお願いします。

お礼日時：2020/08/21 19:36

No.3 回答者： Qchan1962 回答日時： 2020/08/21 13:24

こんにちは、

横から失礼します
＞G35:G39セルに空欄のまま何も入れなくて、G40セルの数字の大小により表示される様にしたい。
G40セルはどのような操作で数値が変わるのですか？
G34を変更した時であれば、実行トリガーを増やし、対応した処理を追加すれば対応できると思いますが、

使用要件、実行条件（付けたしで増えている仕様）を精査した方が良いと思います。
例えば、G40は合計なのでしないと思いますが、添付図のようにG35:G39が空白でも実行するのか？とか、、

定数　10000　は変更するようにするとか、、、
課題が出てきそうですが、、、取敢えず。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim myRng As Range
　　Set myRng = Range("G35:G39")
　　If Intersect(Target, Range("G34", myRng)) Is Nothing Then GoTo Ext　'　追加変更
　　Application.EnableEvents = False
　　If Target(1).Value <> "" Then
　　　　If Target.Address(0, 0) <> "G34" Then　 '　追加
　　　　　　With Target
　　　　　　　　On Error GoTo Ext
　　　　　　　　Call Val_Offset(Target, myRng)
　　　　　　End With
　　　　　　Range("G40").Value = Range("G34") - Application.Sum(myRng)
　　　　　　With Range("G40")
　　　　　　　　If Application.CountIf(myRng, "<>") = myRng.Cells.Count Then
　　　　　　　　　　Call Val_Offset(Target, myRng)
　　　　　　　　　　If Application.CountIf(myRng.Offset(, 6), "このセルに指定数字を入力") _
　　　　　　　　　　　　　　+ Application.CountIfs(myRng.Offset(, 6), "", myRng, "") = 0 _
　　　　　　　　　　　　　　Then
　　　　　　　　　　　　.Offset(, 4).Select
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　End With
　　　　Else　　'　以下追加
　　　　　　Range("G40").Value = Range("G34") - Application.Sum(myRng)
　　　　　　'　　　　　　Range("G40").Formula = "=G34-SUM(G35:G39)"
　　　　　　Call Val_Offset(Range("G40"), myRng)
　　　　End If　　 ' If Target.Ad.　対応
　　End If　'If Target(1).....　対応
　　If Target(1).Value = "" Then Target.Offset(, 4) = ""
Ext:
　　Application.EnableEvents = True
End Sub

Sub Val_Offset(Target As Range, myRng As Range)
　　With Target
　　　　If .Value < 10000 Or Not IsNumeric(.Value) Then
　　　　　　.Offset(, 4).Value = 0
　　　　Else
　　　　　　.Offset(, 4).Value = "このセルに指定数字を入力"
　　　　　　If Not Intersect(Target, myRng) Is Nothing Then .Offset(, 4).Select
　　　　End If
　　End With
End Sub

こんな使い方かなと、Select条件を増やしてみました。。
同様な処理が複数出るので纏めました。
Worksheet_Changeなので、Application.EnableEvents = Falseを追加しました。
もし試されてデバッグし途中で終了した場合、Falseのままなので
Sub RESET_EnableEvents()
Application.EnableEvents = True
End Sub
を実行してください。
判り難くなってしまったら、ごめんなさい。

この回答へのお礼

有り難う御座います。ご指導頂いた次のコードを試行しました。
当初全て空欄の時、G34に数値を入れるとG40に正しく数値を判別した表示がでます。その後、品目ごと（G35～G39セル）に数値を入れて行くと最後のM40セルの数値が10000未満になってもM40の表示が変りませんでした。またG列の数値を消して空欄にし数値がG34=G40なってもM40の表示が変りません。
　①当初G40セルはG34に合計値を手入力後、同数値を表示します。
　②M40はG35:G39が空欄でも実行します。
　②G35～G3９に数値を手入力するとその合計値をG34セル値から引いた数値がM40に表示されます。
　　（G35～G39の数値により、M40の数値は変化します。）
　③定数の10000は変更しません。　　　私の説明が不十分ですみません。よろしくお願いします。

　Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Range("G35:G39")
If Intersect(Target, Range("G34", myRng)) Is Nothing Then GoTo Ext '　追加変更
Application.EnableEvents = False
If Target(1).Value <> "" Then
If Target.Address(0, 0) <> "G34" Then '　追加
With Target
On Error GoTo Ext
Call Val_Offset(Target, myRng)

※ （途中　省略しています。）

End With
End Sub

Sub RESET_EnableEvents()
Applicaition.EnableEvents
End Sub

お礼日時：2020/08/21 17:28

No.2 回答者： めぐみん_ 回答日時： 2020/08/21 10:17

No.1です。

ちょっと違ったかな？

＞If Intersect(Target, Range("G35:G39")) Is Nothing Then Target.Offset(, 4).Select

最後のこれは

If Intersect(Target, Range("G35:G39")) Is Nothing Then End Sub

かな？

この回答へのお礼

有り難う御座います。頂いたコードを試行しました。G40の数値が変化（基準10000以上、未満で表示が変る。）してもM4０セルの表示が変りませんでした。教えて下さい。

お礼日時：2020/08/21 17:34

No.1 回答者： めぐみん_ 回答日時： 2020/08/21 10:06

＞g = Range("G34").Value - Application.Sum(Range("G35:G39"))

g = Range("G40").Value

でも良いと後から感じましたが 、今回の質問の場合セル範囲：Ｇ３５～Ｇ３９　の値変更がイベント発生のトリガーになってますので
それ以外でのセル値変更ででもとしたいとなると、

Private Sub Worksheet_Change(ByVal Target As Range)
Dim g As Long
If Not Intersect(Target, Range("G35:G39")) Is Nothing Then
If Target(1).Value < 10000 Or Not IsNumeric(Target.Value) Then
'MsgBox ("M" & Target.Row & "セルに 「0」を入力")
Target.Offset(, 4).Value = 0
Target.Offset(, 4).Select
Else
'MsgBox ("M" & Target.Row & " セルに指定数字を入力")
Target.Offset(, 4).Value = "このセルに指定数字を入力"
Target.Offset(, 4).Select
End If
End If

g = Range("G40").Value

If g < 10000 Then '未満の場合、以下ならｇ<＝10000
'MsgBox("M40" & Target.Row & "セルに「0」を入力")
Range("M40").Value = 0
Else
'MsgBox("M40" & Target.Row & "セルに指定数字を入力")
Range("M40").Value = "このセルに指定数字を入力"
End If

If Intersect(Target, Range("G35:G39")) Is Nothing Then Target.Offset(, 4).Select

になるのかも。

この回答へのお礼

有り難う御座いました。頂いたコードをNo.2で試行しました

お礼日時：2020/08/21 17:34