思考酒後

自分に入ってきた情報を定着、深化するために文章化

MENU

【Excelマクロ】セルの入力値の判定をして色を付けるマクロを作ったよ。


 こんにちは、自称Excel通のmasa@ masaです。
 
 いきなりですが、人から貰ったExcelをチェックするとき最初にどんなことをしますか?
 人から貰ったExcelをチェックするときに僕はまず、入力されている値の状態を明確にしてからチェックをしています。
  • 直接入力
  • 数式入力
  • 文字列
  • 未入力

 セルに対して上記4状態のうち『どの状態であるか』を明確にしてからチェックをしていて、今回はこの作業を自動化するためにコードを作ってみたのでで記事を書いていこうと思います。

 具体的には①自分が選択した範囲で、②選択した範囲のセルでどの状態であるか判別し、③定義した色に着色する。という方法です。

 

f:id:masa_mn:20171029131014j:plain

 

結論:セルの入力値を判定するコードは、こんなコードです。 

 いきなり結論ですがこんなコードです。ざっくり説明すると前半は選択範囲を取得するコードで、後半は入力値の判定+色付けするコードという構成になっています。

Sub セルの入力値判定()

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer

Dim i As Integer
Dim j As Integer

a = Selection(1).Row
b = Selection(Selection.Count).Row
c = Selection(1).Column
d = Selection(Selection.Count).Column

 For i = a To b
  For j = c To d

'数式ならば:青色
  If Cells(i, j).HasFormula Then
  Cells(i, j).Interior.Color = 16764006
'未入力ならば:透明
  ElseIf Cells(i, j) = "" Then
  Cells(i, j).Interior.Pattern = xlNone
'数値ならば:桃色
  ElseIf IsNumeric(Cells(i, j)) = True Then
  Cells(i, j).Interior.Color = 16764159
'文字列ならば:緑色
  ElseIf VarType(Cells(i, j)) = vbString Then
  Cells(i, j).Interior.Color = 10092441
  End If

  Next j
 Next i

End Sub

 

後半:入力値の判定+着色

 標題のように「入力値の判定」とあるので先に入力値を判定するコードについて書いていきます。

 ①数式のときの判定

 数式のときは『セル』.HasFormuolaというコードで判定できます。Thenより後の部分は数式であるときにセルを青色に色付けするというコードです。

  If Cells(i, j).HasFormula Then

       Cells(i, j).Interior.Color = 16764006

 

②空白のときの判定

 空白のときは 『セル』=""というコードで判定できます。Thenより後の部分は数式であるときにセルを透明にするというコードです。

 複数条件を判定したいときに2個目以降はElseifを用いることで増やすことができます。余談ですがVBAのIF関数はExcel関数と違い、デフォルトでNoの場合の条件を与えないという違いがありますね。自分で与える場合はElseが有効です。

  ElseIf Cells(i, j) = "" Then
  Cells(i, j).Interior.Pattern = xlNone

 

③数値のときの判定

 数値のときは Numeric(『セル』)=Trueというコードで判定できます。Thenより後の部分は数式であるときにセルを桃色に色付けするというコードです。

  ElseIf IsNumeric(Cells(i, j)) = True Then
  Cells(i, j).Interior.Color = 16764159

 

④文字列のときの判定

  文字列のときは VarType(『セル』)=vbStringというコードで判定できます。Thenより後の部分は数式であるときにセルを緑色に色付けするというコードです。

  ElseIf VarType(Cells(i, j)) = vbString Then
  Cells(i, j).Interior.Color = 10092441 

 

前半:自分の選択した範囲の取得

 対象範囲をシート全体ではなく自分が選択した範囲としたかったのでセルの範囲Cells(i,j)を取得する必要があると考えました。

 シート全体としたいのであれば、a=1,b=200【シート最深部を想定】,c=1,d=200【シート最深部を想定】とコードを書きかえればいいと思います。

 

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer

Dim i As Integer
Dim j As Integer

a = Selection(1).Row
b = Selection(Selection.Count).Row
c = Selection(1).Column
d = Selection(Selection.Count).Column

 For i = a To b
  For j = c To d

 

~略~


  Next j
 Next i

 変数iは行の最初aと行の最後b、変数jは列の最初cと列の最後dとし、それぞれの数値は図1から取得しました。

 

図1:a~dの定義 とa~dを取得するためのコード

f:id:masa_mn:20171029134210j:plain

参考:Office TANAKA - Excel VBA Tips[選択範囲の操作]

 

  ちなみに下記のように変数iの次に変数jがくる構造とした場合、iを固定してjを全範囲変化させて、次のiを固定してjを全範囲変化させるという変数の流れとなります。

 変数iは変数jの外側にくるイメージですね。

 For i = a To b
  For j = c To d

~略~
  Next j
 Next i

 

 

実行結果

▼実行前。サンプルとして以下のExcelを用意しました。

f:id:masa_mn:20171029142044j:plain

 

▼実行後。狙い通りの結果となっています。

f:id:masa_mn:20171029142114p:plain

 

おしまい。

 参考文献

参考:Office TANAKA - Excel VBA Tips[選択範囲の操作]

Office TANAKAさんさすがでした。いつもお世話になってます。

 

オススメ

この書籍もかなり分かりやすかったです。マクロなのに小説仕立てという摩訶不思議…。良かったら是非。