Excel 2007のSortオブジェクト

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

セルの背景色や文字の色で並べ替える

Excel 2007からは、セルの文字色・セルの背景色・条件付き書式のアイコン で並べ替えることが可能になりました。

色やアイコンで並べ替えるには、まず引数SortOnを指定します。

定数 数値 意味 既定値
xlSortOnValues 0 セル内のデータで並べ替え ←これ
xlSortOnCellColor 1 セルの背景色で並べ替え
xlSortOnFontColor 2 セルの文字色で並べ替え
xlSortOnIcon 3 条件付き書式のアイコンで並べ替え

ここでは、次のようなデータを例にします。

「名前」列を、赤色の背景色が上になるように並べ替えるには、次のように指定します。

この操作をマクロ記録すると、次のようなコードが記録されます。シートの指定は、ActiveSheetに編集しました。

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("B2:B10"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(230, 184, 183)

前ページで解説したように、SortFieldオブジェクトには並べ替えの条件を指定します。ここでも、Addメソッドで新しい条件を追加していますが、セル内のデータ(数値や文字列)で並べ替えたときと違い、名前付き引数が使われていません。前ページと同じように名前付き引数を使うと、次のようになります。

    ActiveSheet.Sort.SortFields.Add( _
        Key:=Range("B2:B10"), _
        SortOn:=xlSortOnCellColor, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal).SortOnValue.Color = RGB(230, 184, 183)

前ページとの違いは、

  • 引数SortOnに、定数xlSortOnCellColorが指定されている
  • Addメソッドで追加したSortFieldオブジェクトの、SortOnValue.Colorに色を指定している

ということです。つまり、

【セルの背景色で並べ替えるとき】

    ActiveSheet.Sort.SortFields.Add( _
        Key:=Range("B2:B10"), _
        SortOn:=xlSortOnCellColor, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal).SortOnValue.Color = セル.Interior.Color

【セルの文字色で並べ替えるとき】

    ActiveSheet.Sort.SortFields.Add( _
        Key:=Range("B2:B10"), _
        SortOn:=xlSortOnFontColor, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal).SortOnValue.Color = セル.Font.Color

SortOnValue.Colorには、並べ替えたいセルの背景色(Iterior.Color)や文字色(Font.Color)を指定します。

この並べ替えはけっこう簡単ですね。分かりやすい。ちなみに

    ActiveSheet.Sort.SortFields.Add( _
        ・・・・
        ・・・・).SortOnValue.Color = 色

ってことは要するに、Addメソッドで追加したSortFieldオブジェクトの、SortOnValue.Colorに色を設定するわけですから、次のように考えることもできます。

    ActiveSheet.Sort.SortFields.Add _
        ・・・・
        ・・・・
    ActiveSheet.Sort.SortFields(ActiveSheet.Sort.SortFields.Count).SortOnValue.Color = 色

ActiveSheet.Sort.SortFields をWithでくくるとスッキリします。

    With ActiveSheet.Sort.SortFields
        .Clear
        .Add _
        ・・・・
        ・・・・
        .Item(.Count).SortOnValue.Color = 色
    End With

条件付き書式のアイコンで並べ替える

やっかいなのはコッチです。

まずは、条件付き書式を設定するところからやりましょう。

1.条件付き書式を設定したいセル範囲を選択します。なお、アイコンがバラけるように、数値を変更しました

2.条件付き書式のアイコンを設定します

3.こうなります

4.任意のセル(ここではセルC4)を右クリックして、アイコンで並べ替えます

その操作をマクロ記録して得られるコードが下記です。

    ActiveSheet.Sort.SortFields.Add( _
        Key:=Range("C4"), _
        SortOn:=xlSortOnIcon, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal).SetIcon Icon:=ActiveWorkbook.IconSets(14).Item(4)

SetIconメソッドは、ヘルプによると

SortFieldオブジェクトのアイコンを設定します

だそうです。この"アイコン"とは、Iconオブジェクトのことです。ちなみに、記録された IconSets(14) とは、条件付き書式で設定したアイコンセットです。

つまり、条件付き書式のアイコンで並べ替えを行うには、そのセル範囲に設定されているアイコンセットを調べ、さらに、そのセルにどんなアイコンが表示されているかを調べなければなりません。

アイコンセットを取得するのは簡単です。たとえば、今回の例でセル範囲C2:C10に設定されているアイコンセットは、次のようにして取得できます。

Range("C2:C10").FormatConditions(1).IconSet.Id

今回なら、設定したアイコンセットのIdプロパティである「14」が返ります。SortFieldオブジェクトを設定する前に、このIdプロパティを変数か何かに入れておき、ActiveWorkbook.IconSets(■) に指定すればいいです。問題は、そのセルにどんなアイコンが表示されているかです。現在のところ、うまい方法が思いつきません。もちろん、各アイコンの"しきい値"を調べて、セル内の値がどこに該当するかを計算すれば、表示されているであろうアイコンを特定できますが、かなり面倒な作業です。たかが表示されているアイコンを調べるためにやる作業ではありませんね。

余談ですが。

Excelは、条件付き書式の結果、現在の書式がどうなっているかを取得するのが大変でした。

たとえば、

セルB2に「10」と入力しています。書式などは何も設定していません。セルの背景色を調べると

Sub Sample2()
    MsgBox Range("B2").Interior.ColorIndex
End Sub

「-4142」は、セルの背景色が「色なし」であることを示す、定数xlNoneの数値です。では、このセルに条件付き書式を設定してみましょう。もし、セルB2の値が50より大きかったら、背景色を赤色にするという条件付き書式です。

この状態で、セルB2に「60」と入力すれば、もちろん条件付き書式によって、セルの背景色が赤色に変わります。

では、ここでさっきのマクロを実行してみましょう。

Sub Sample2()
    MsgBox Range("B2").Interior.ColorIndex
End Sub

結果は変わりません。条件付き書式によって変化した書式は、Rangeオブジェクトから取得できなかったんです。条件付き書式の結果、現在のセルB2がどうなっているかを判定するには、次のようにやるしかありませんでした。

  1. セルB2に設定されている条件付き書式の「書式」を調べる(背景色が赤)
  2. セルB2に設定されている条件付き書式の「条件」を調べる(>50)
  3. セルB2が、その「条件」を満たしているかを調べる(「=B2>50」がTrueか)

そんなこといったって、設定されている書式が1つとは限らないし、じゃぁ、セルの書式全部調べろってか？条件の数式だって簡単とは限らないし、相対参照で設定されていたら、現在のアクティブセルによって数式のアドレスが変わってくるし・・・大変です。

だからでしょうか。今さら感は否めませんが、Excel 2010ではDisplayFormatオブジェクトが追加されました。これは、条件付き書式によって現在表示されている書式を表すオブジェクトです。このDisplayFormatオブジェクトを使うと、次のようになります。

Sub Sample3()
    MsgBox Range("B2").DisplayFormat.Interior.ColorIndex
End Sub

ただし、DisplayFormatオブジェクトで取得できるのは、Rangeオブジェクトのプロパティだけです。条件付き書式の結果表示されているアイコンを取得することはできません。このアイコンは、Iconオブジェクトですし、アイコンのセットもIconSetオブジェクトです。何とかならないものでしょうかね。

まぁ、私のカンでは、条件付き書式のアイコンって、あまり使われないような気がします。誰もが、ひんぱんに使うとは思えません。ましてや、そのアイコンで並べ替えたいというニーズは、さらに少ないでしょう。つまり、あまり使う人はいないと。だから、任意のセルに、現在表示されているアイコンが何かを簡単に取得できなくても、別にいいのかな、と。

並べ替えを実行する >> 次ページ