Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2013)
値を入れたら自動でセルの色を変えたい
投稿日時: 21/02/19 01:45:30
投稿者: くろんぼ

セルにAを入れたら●、Bを入れたら青、Cを入れたら赤、Dを入れたら緑と自動で変えたく
以下のようなコードを作成しました。
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange, selectranger As Range
Dim ColorType As String
    If Selection.Cells.CountLarge > 1 Then
        For Each selectrange In Selection
            If selectrange.Value = "" Then
                selectrange.Interior.Color = RGB(255, 255, 255)
            End If
        Next
    ElseIf Target.Row >= 24 And Target.Column >= 8 And Target.Column <= 190 Then
        ColorType = Target.Value
        Select Case ColorType
            Case ""
                Target.Interior.Color = RGB(255, 255, 255)
            Case "A"
                Target.Interior.Color = RGB(0,0,255)
        Target.value="●"
            Case "B"
                Target.Interior.Color = RGB(255,0,0)
            Case "C"
                Target.Interior.Color = RGB(0,255,0)
         End Select
 
    End If
 
End Sub
 
複数セルを選択してDeleteした場合、エラーが起きていたので、上記のようなコードを作成しました。
手作業でA,B,Cを入力したり、値をDeleteする場合は上手く動くのですが、別で以下のようなコード(抜粋)で自動でコピペすると赤字のコードのところでエラーが出ます。なぜでしょうか。
 
     With ThisWorkbook.Worksheets("日程")
                         
     EndRowB = .Cells(Rows.Count, 3).End(xlUp).Row
     .Range(.Cells(EndRowB, 2), .Cells(EndRowB + 5, 191)).Copy .Cells(EndRowB + 6, 2)
     .Range(.Cells(EndRowB + 6, 8), .Cells(EndRowB + 11, 190)).ClearContents
     .Cells(EndRowB + 6, 2) = .Cells(EndRowB, 2).Value + 1
 
 
 

回答
投稿日時: 21/02/19 08:33:15
投稿者: simple

コードが部分なので詳細不明ですが、
その一文については文法上の誤りはないものと思います。
 
具体的なエラーメッセージを省略せずに書いてもらえますか?

回答
投稿日時: 21/02/19 09:35:22
投稿者: Suzu

問題のマクロの実行では、複数のセルのクリアを行っています。
 
その時、

引用:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange, selectranger As Range
Dim ColorType As String
    If Selection.Cells.CountLarge > 1 Then
        For Each selectrange In Selection
            If selectrange.Value = "" Then
                selectrange.Interior.Color = RGB(255, 255, 255)
            End If
        Next
    ElseIf Target.Row >= 24 And Target.Column >= 8 And Target.Column <= 190 Then
        ColorType = Target.Value

 
の部分を処理します。
 
このとき、Targetは 複数の Rangeオブジェクトですから Valueは、配列Variant です。
直前に、Debug.Print TypeName(Target.Value) とでもすれば確認できますよね。
 
代入しようとしている ColorType のデータ型は何ですか?

回答
投稿日時: 21/02/19 10:38:54
投稿者: simple

質問を読み違えていたようです。失礼。
 
先を急いでしまってすみませんが、
イベントプロシージャはこうなるんじゃないですかね。
 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Dim r As Range
    Dim LastRow As Long

    LastRow = Rows.Count
    Set myRange = Intersect(Target, Range(Cells(24, 8), Cells(LastRow, 190)))

    If myRange Is Nothing Then Exit Sub

    For Each r In myRange
        Select Case r.Value
            Case ""
                r.Interior.ColorIndex = -4142
            Case "A"
                r.Interior.Color = RGB(0, 0, 255)
                Application.EnableEvents = False
                r.Value = "●"
                Application.EnableEvents = True
            Case "B"
                r.Interior.Color = RGB(255, 0, 0)
            Case "C"
                r.Interior.Color = RGB(0, 255, 0)
        End Select
    Next
End Sub

回答
投稿日時: 21/02/19 19:00:54
投稿者: WinArrow
投稿者のウェブサイトに移動

一般機能の「条件付き書式」では対応できませんか?

投稿日時: 21/02/19 19:15:12
投稿者: くろんぼ

みなさん
ありがとうございます。
 
>Simpleさん
せっかくご提案頂きましたが、Suzuさんのご指摘を確認、修正したら、上手く動くようになりました。
 
>Suzuさん
ColorType=Target.value→ColorType=Target.Text
に修正したところ、上手く動くようになりました。
ただ、完了するまでに時間がかかるのですが、作成したコードが良くないのでしょうか?
もっと早くするようなコードのヒントがあれば、教えて頂きたいです。
 
>Win Arrowさん
「条件付き書式」でできれば良いと思っていたのですが、Aと入力した時に●と表示させたいので、条件付き書式設定では無理かと思い、VBAにしましたが、方法はありますでしょうか?

投稿日時: 21/02/19 19:36:26
投稿者: くろんぼ

>Win Arrowさん
 
入力をアルファベット(A)ではなく数字にすれば●に変換できることがわかりました。
この方法でも考えてみたいと思います。
 
>Suzuさん
 
条件付き書式で文字列に変換できるのは3つまでと記載がありました。
今後、4つ以上の文字列に変換したいと要求される可能性もあるので、VBAでも考えておきたいです。
もし、速度を早くするヒントがあれば、お願いします。

回答
投稿日時: 21/02/20 00:04:30
投稿者: WinArrow
投稿者のウェブサイトに移動

>条件付き書式で文字列に変換できるのは3つまでと記載がありました。
多分、Excel2003までの説明を見たと思います。
 
Excel2007以降は、幾つでm問題ないですよ!!!

投稿日時: 21/02/20 00:48:19
投稿者: くろんぼ

WinArrowさん
申し訳ありません。
発見したのは条件付き書式ではなく、セルの書式設定のユーザー定義での設定で
以下のように記述する方法でした。
[=1]"●";[=2]"×";"■"
条件付き書式でも文字列に変換できるのでしょうか?

回答
投稿日時: 21/02/20 06:17:38
投稿者: simple

コードだけで説明がなかったので、意図が通じなかったようです。
 
焦点は条件付き書式に移ったようですが、
追加説明して、マクロコードに一応の決着を付けて置きたいと思います。
 
(1)Changeイベントプロシージャのなかで、Selectionに依存するのは間違いだと思います。
   例えば、
   .Range(.Cells(○ ,○ ), .Cells(○ ,○ )).ClearContents
   のような形式で、複数セル範囲を消去するとき、
   Selectせずに、消去処理は行われるので、
   Selectionは、Targetとはまったく関係がないものを指しています。
    
   Selectionがたまたま複数セルであることも、当然あり得ます。
   その場合は、Selection.Cells.CountLarge > 1 が True になり、
   前半の処理は行われますが、後半の処理は実行されないことになります。
    
   つまり、実行されるのは、Targetとは無縁のSelectionに対する前半の処理だけです。
   Targetの塗りつぶし色が白になることはありません。
    
(2)逆に、複数セル範囲に、コピーペイスト処理などで、
   同時に A や B に変更することは無いんですか?
   その場合には、想定された動きにはなりませんよ?
   (a)Selectionがたまたま複数セルなら、前半処理が行われるだけですし、
   (b)Selectionが単一処理の場合は、後半処理が行われますが、
      Targetの個々のセルが正常に処理されるようには(少なくとも提示コードでは)
      なっていません。
 
従って、私が書いたように、
   ●Changeイベントプロシージャの各種の判断に、Selectionは関係させず、
     Target(変更があったセル範囲)だけで判断すべきです。
      
   ●複数セルを同時に変更するケースにも、対応する必要があるのではないですか?
     こうしておけば、
       ・消去のみならず、
       ・手作業によるコピーペイスト
       ・マクロによる同時変更
     などにも対応ができるはずです。
 
また、changeイベントプロシージャの中でセルを変更すると、
再入が起き、Changeプロシージャが再度実行されます。

この例では無限繰り返しは起きませんが、無駄な再実行が発生していることは確かです。
このことは常に意識しておき、繰り返しが発生しない手当
(Application.EnableEventsを一時的にFalseにしておき、処理後Trueに戻す)
を行う習慣にしておくとよいでしょう。

回答
投稿日時: 21/02/20 13:26:17
投稿者: WinArrow
投稿者のウェブサイトに移動

くろんぼ さんの引用:
WinArrowさん
申し訳ありません。
発見したのは条件付き書式ではなく、セルの書式設定のユーザー定義での設定で
以下のように記述する方法でした。
[=1]"●";[=2]"×";"■"
条件付き書式でも文字列に変換できるのでしょうか?

 
条件付き書式にこだわって・・・・
>[=1]"●";[=2]"×";"■"
この条件の1つを条件付き書式の条件にすれば、
何個でも対応可能ですよ!
 
 
1つ目の条件
=1
表示形式
[=1]●
 
2つ目の条件
=2
表示形式
[=2]×
 
 
4つ目の条件
=4
表示形式
[=4]▲
 
 
 
 

投稿日時: 21/02/20 20:04:46
投稿者: くろんぼ

>Simpleさん
 
ご指摘の内容に修正し、上手く動くようになりました。
また、速度も速いです!
ありがとうございました。
 
>WinArrowさん
 
条件付き書式でもできました!
ありがとうございました。
 
VBA版を使用するか条件付き書式版を使用するかは周りの意見を聞いて決めようと思います。