エクセルでロットの更新

質問

専門家の人にお尋ねします。
商品が2001年8月25日に作った物が有るとします。
その時のLOTは1825XXXXで、LOT内容は左から1が（２００１年）8が（８月）25が（２５日）とします。後の４桁は連番です。１０月だとＸ　１１月だとＹ　１２月だとＺになる決まりがあります。
結果判断の内容は指定した月日以内をこえると警告（色を付ける）の指定です。
例えばセルA1に警告月4と入力し　A5にロットを入力しまするとOKならそのまま、NGなら赤色になります。ただネックなのがXYZでどうすれば月の判断が出来るのかわかりません。VBでかまわないので、解決する方法は無いでしょうか？

回答No.4

nishi6 · ベストアンサー

＞1.空白又A5のセルを削除した場合エラ―出る。　
　　　対応しました。考慮不足でした。
＞2.A5だけでは無く複数のセル（A5～A10）は可能？
　　　対応しました。範囲は変更できるよう分かりやすく書いたつもりです。
＞3.シート名を指定
　　　先ほどのコードは、この機能を使いたいシートのコードウインドウに貼り付ければいいんですが
　　　たくさんあると大変？下記はシート名を指定できるようにしてあります。ThisWorkBookに貼り付けます。

＜ThisWorkBookのコードウインドウに貼り付けます＞
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
　　'例　Sheet1かSheet2でなければ何もしない
　　If Not (Sh.Name = "Sheet1" Or Sh.Name = "Sheet2") Then Exit Sub
　　'単一のセルの変更のみ対象
　　If Not (Target.Rows.Count = 1 And Target.Columns.Count = 1) Then Exit Sub
　　'対象セルを消去した場合の対応
　　If Target = "" Then Target.Font.ColorIndex = xlAutomatic: Exit Sub
　　'A5からA10に含まれる場合のみ処理する
　　If Union(Range("A5:A10"), Target).Address <> Range("A5:A10").Address Then Exit Sub

　　Dim LOT As String 'LOT
　　Dim LOTnen As Integer 'LOTの年
　　Dim LOTtuki As Integer 'LOTの月
　　Dim keikokuNen As Integer '警告年
　　Dim keikokuTuki As Integer '警告月

　　On Error GoTo ErrorHandler

　　LOT = Right("00000000" & Target, 8) 'セルが数値形式の場合の対応
　　LOTnen = 2000 + Left(LOT, 1)
　　LOTtuki = InStr("123456789XYZ", Mid(LOT, 2, 1))
　　keikokuNen = Year(Now())
　　keikokuTuki = WorksheetFunction.Substitute(Range("A1"), "警告月", "")
　　'判定
　　If keikokuNen * 12 + keikokuTuki < LOTnen * 12 + LOTtuki Then
　　　　Target.Font.ColorIndex = 3 '赤
　　Else
　　　　Target.Font.ColorIndex = xlAutomatic '黒
　　End If

　　Exit Sub
ErrorHandler:
　　MsgBox "エラーです"
End Sub

お礼

アドバイス有難う御座いました。
期限を変更にした場合は更新が出来なかったは残念ですが
とても良く作ってあり感謝しております。
ポイント入れておきます。

回答No.3

comv

こんにちは！

回答では無いのですが！

EXCELのバージョンは？
97/95だと1/8/25で平成１年８月２５日と判断してしまい
2000だと２００１年８月２５日と判断する違いがあります
したがって97以前を使う可能性があるなら、そのてんの
考慮も必要になります。

＞LOT内容は左から1が（２００１年）
・2010年はどのようになるのでしょうか？（記号かな？）

＞結果判断の内容は指定した月日以内をこえると
＞警告（色を付ける）の指定です。
＞例えばセルA1に警告月4と入力し
・月で判定？　月日で判定？　どちらでしょうか？

いずれにしろ条件付き書式の条件としては
［数式が］でロット番号を年月日に加工することになる
とかな？
もしくは　AND(年>=○,月>=△,日>=□)で分解した数値を
比較させる手もあるでしょう！

参考としてロット番号を200X年を基準として年月日に加工する式のみ記載させて頂きます。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(200&REPLACE(REPLACE(LEFT(A5,4),3,,"/"),2,,"/"),"X",10),"Y",11),"Z",12)*1

補足

20010年は0になります。又、試したのですが、数字しか表示されませんでした。

回答No.2

nishi6

専門家ではありませんがよろしいでしょうか。

質問から、対象セルはA5のみとしています。赤色は文字の色と解釈しました。
『指定した月日以内をこえる』とありますが、警告月を超えたら赤にしています。日の指定が無い？
また、警告年はどのように判定するのでしょう。警告月を1月等にすると、年が重要になってきます。今はNow()を使っています。
警告月が10月の場合は、『警告月10』と入力するとしました。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Address <> "$A$5" Then 'A5以外なら何もしない
　　　　Exit Sub
　　End If

　　Dim LOT As String 'LOT
　　Dim LOTnen As Integer 'LOTの年
　　Dim LOTtuki As Integer 'LOTの月
　　Dim keikokuNen As Integer '警告年
　　Dim keikokuTuki As Integer '警告月

　　LOT = Target
　　LOTnen = 2000 + Left(LOT, 1)
　　LOTtuki = InStr("123456789XYZ", Mid(LOT, 2, 1))
　　keikokuNen = Year(Now())
　　keikokuTuki = WorksheetFunction.Substitute(Range("A1"), "警告月", "")
　　'判定
　　If keikokuNen * 12 + keikokuTuki < LOTnen * 12 + LOTtuki Then
　　　　Target.Font.ColorIndex = 3 '赤
　　Else
　　　　Target.Font.ColorIndex = xlAutomatic '黒
　　End If
End Sub

お礼

アドバイス有難う御座います。
貴方様を見習って頑張ります。

補足

とても良く出来たVBの内容でした。
有難う御座います。
試したのですが3点追記出来ないでしょうか　
1.　空白又A5のセルを削除した場合エラ―出る。　
2.　A5だけでは無く複数のセル（A5～A10）は可能？
3.　シート名を指定
これが可能であれば、私が思っている内容です。
あともう一歩何卒助けて下さる様お願い申し上げます。

回答No.1

table_1969

Ａ５の「条件付き書式」はどのようなものでしょうか？
別に月の判断をしなくても、
Ａ５に「条件付き書式」で（数式が）=RIGHT(A1,1)=MID(A5,2,1)とし書式を(パターン)赤を選べばＯＫじゃないんでしょうか？

お礼

アドバイス有難う御座います。

補足

2002年の場合はどうするのでしょうか？