[PR]ネットとスマホ OCNでまとめておトク!

セルに条件付書式で書式設定してあります。

A1は「値」100以下
A2は「値」500以下
A3は「値」1~10の間
B1は「数式」で=B1<A1
以下さまざまな数式があります。

条件に一致すると、セルの文字が「赤」になります。

このとき、
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Font.ColorIndex
End Sub
を実行しても、ColorIndexは、赤の「3」ではなく「-4105」と表示されます。
「-4105」は何もフォントの色を指定してないセルでも同じく表示されます。

質問1.条件付書式で、条件が一致して表示されたフォントの色は取得できないのでしょうか?

質問2.-4105とは何でしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (13件中1~10件)

merlionXXさん、いつも、スレッドでおさがわせしてすみません。



今回は、別なところで、KenKen_SPさんのコードに感化されまして、私としては、良い勉強させていただいております。

さて、
>No4で教えていただいたコートが「数式が」しか対応しないなら、条件付書式の方を「セルの値が」を「数式が」に変えてやればいいわけですよね?

#9 のコードは、「数式が」でも、「セルの値が」でも、両方使えるはずだと思って作っております。

If .FormatConditions(i).Type = 2

というところで、振り分けをしています。

>「値」の定義?(等しい、間、以上、以下等)ってすべて数式で表せますよね?

一応、すべて当たってみたつもりですが、なにぶんにも夕飯前でしたので、万が一にも、おかしいようでしたら、
FormatCndOperand 関数の myOperand の中の配列の中にある文字列の等号式がおかしいことになります。
    • good
    • 0
この回答へのお礼

何度もありがとうございました。

> #9 のコードは、「数式が」でも、「セルの値が」でも、両方使えるはずだと思って作っております。

はい、完璧でした。
いつもありがとうございます。
これからもよろしくお願いいたします。

お礼日時:2005/06/20 11:10

こんにちは。

KenKen_SPです。

>これでやっと色のインデックスを取得できましたが、こういうやり方
>でいいんですね?

そのとおりです。

既に Wendy02 さんのコードで解決となりますし、ご質問の趣旨から少し
はずれるかもしれませんので、ためらいがあったのですが、今後同様の、
つまり「今度はセルの背景色」といった場合にも応用できるように、との
意図を込めて #10 の関数化したコードをアップさせていただきました。

また補足として、条件付き書式を設定する際にユーザーが行う可能性が
ある操作について、思いつく限り配慮したものになっています。

例えば 条件付き書式の設定では、下限値と上限値を逆さまに設定しても
正常に動作します。また、これらの数値は、セル参照で設定することも
可能です。

これらに対応するために、#10 のコードでは、サブプロシージャの Swap
を設けたり、Formula1 や Formula2 プロパティー値がセル参照式であって
もエラーとならないように、

f2 = Evaluate(.Formula2)

として値で評価する仕組みになっています。
    • good
    • 0
この回答へのお礼

ありがとうございました。
とても勉強になりました。

お礼日時:2005/06/19 14:25

こんにちは。

KenKen_SPです。

>...Functionってどうやってつかうんでしょうか?

#10の「以下コード」から終わりまでを標準モジュールにコピー&ペースト
します。

あとは、マクロで LEN や MID などと同様に関数としてコードで使うことも
できますし、ワークシートでも通常の関数のようにも使えます。

条件付き書式は 3 つの条件を設定することができますが、ユーザー定義関数
GetCndNum は引数で渡したセルの「条件付き書式」の条件を調べ、もし発動
しているなら、その条件番号 1~3 のいづれかを返します。

もし、「条件付き書式」が設定されていなかったり、発動していなければ
0 を返します。


・コードで使う場合--------------------------------------------------

Dim F as Integer
F = GetCndNum(Range("A1"))

でセルA1に設定された条件付き書式で、変数 F には発動している条件付き
書式の条件番号が返ります。0 が返ってきたら条件付き書式は発動していま
せん。

条件付き書式の発動条件の番号を調べられれば、あとは知りたい内容で関数
を新たに作ることは容易です。

例えば、#10の再掲になりますが

Function GetColorIndex(Target As Range)

  Dim F As Integer
  F = GetCndNum(Target)
  If F Then
    GetColorIndex = _
    Target.FormatConditions(F).Font.ColorIndex'-----(A)
  Else
    GetColorIndex = 0 '条件付き書式の色だけほしいのであえて 0
  End If

End Function

とコードを標準モジュールに書けば、カラーインデックスが取得できます。
セル背景色を取得したければ、(A)の部分を

Target.FormatConditions(F).Interior.ColorIndex

と書き換えれば OK です。

このように、ユーザー定義関数は通常の LEN や YEAR 関数のようにコード
の中で使用することができます。


・ワークシートで使う場合----------------------------------------------

標準モジュールにコードを貼り付けるとワークシートでもユーザー定義関数は
使用することが可能です。

1. A1セルに条件付き書式を設定
2. B1セルに計算式 =GetCndNum(A1) を入力

以上でA1セルにデータ入力し、それが 1. で設定した条件を満たせば、その条件
番号が得られます。

先述のユーザー定義関数 GetColorIndex も標準モジュールに貼り付けであれば、
C1 セルに計算式 =GetColorIndex(A1) と入力すると、C1 にはカラーインデックス
が表示されます。

この回答への補足

Function GetColorIndex(Target As Range)
  Dim F As Integer
  F = GetCndNum(Target)
  If F Then
    GetColorIndex = _
    Target.FormatConditions(F).Font.ColorIndex'-----(A)
  Else
    GetColorIndex = 0 '条件付き書式の色だけほしいのであえて 0
  End If
End Function

も標準モジュールに記入。

シートのコードに
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox GetColorIndex(Target)
End Sub

これでやっと色のインデックスを取得できましたが、こういうやり方でいいんですね?

補足日時:2005/06/18 22:49
    • good
    • 0

merlionXX さん、Wendy02 さん、レスありがとうございました。



いやー、#8で放置してくれ、、とか書きましたけど、本当に放置されなくて
うれしいです。

何度も挫折しかかりましたが、何とかコードを書いてみました。ロジックは
ほとんど Wendy02 さんから教えていただいたとおりです。#9 で既に解決かと
思いますし、蛇足かもしれませんが、結構頑張って書いたので、折角だから
アップさせて下さい。

Wendy02さん、さすがでございます(^^)


GetCndNum という関数にしてあります。引数に指定されたセルの条件付き書式
が True ならば、その条件番号を返します。

こうすることのメリットは、例えば今回のような色を取得したといった場合、
次のような関数を作成する際に再利用できそうだからです。

Function GetColorIndex(Target As Range)

  Dim F As Integer
  F = GetCndNum(Target)
  If F Then
    GetColorIndex = _
    Target.FormatConditions(F).Font.ColorIndex'-----(A)
  Else
    GetColorIndex = 0 '条件付き書式の色だけほしいのであえて 0
  End If

End Function

応用して(A)で読み取るプロパティーを変えると様々な情報が得られます。


'-------------------- 以下コード ------------------------------------

'引数で指定したセルにおいて、そこに設定された条件付き書式の
'条件を満たすか調べ、該当する最優先の条件番号を返す
'条件を満たさばければ0 を返す
Function GetCndNum(rngCel As Range) As Integer

  Dim f0, f1, f2
  Dim i As Integer
  Dim flag As Boolean

  On Error GoTo ErrorHandler

  GetCndNum = 0
  If rngCel.FormatConditions.Count = 0 Or _
    rngCel.Count > 1 Then Exit Function

  '条件付き書式の条件を満たすか評価
  flag = False
  f0 = Evaluate(rngCel.Value)
  For i = rngCel.FormatConditions.Count To 1 Step -1
    With rngCel.FormatConditions(i)
      If .Type = 2 Then
        '2:xlExpression 「式が」の場合
        If Evaluate(.Formula1) Then flag = True
      Else
        '1:xlCellValue 「セルが」の場合
        f1 = Evaluate(.Formula1)
        Select Case .Operator
          Case xlBetween
            f2 = Evaluate(.Formula2)
            If f1 > f2 Then Swap f1, f2
            If f1 <= f0 And f0 <= f2 Then flag = True
          Case xlEqual
            If f1 = f0 Then flag = True
          Case xlGreater
            If f1 > f0 Then flag = True
          Case xlGreaterEqual
            If f1 >= f0 Then flag = True
          Case xlLess
            If f1 < f0 Then flag = True
          Case xlLessEqual
            If f1 <= f0 Then flag = True
          Case xlNotBetween
            f2 = Evaluate(.Formula2)
            If f1 > f2 Then Swap f1, f2
            If Not (f1 <= f0 And f0 <= f2) Then flag = True
          Case xlNotEqual
            If f1 <> f0 Then flag = True
        End Select
      End If
    End With
    '戻り値セット
    If flag Then
      GetCndNum = i
      Exit For
    End If
  Next i
  Exit Function

ErrorHandler:
  Err.Clear

End Function

'値の入れ替え
Private Sub Swap(ByRef f1, ByRef f2)
  Dim tmp
  tmp = f1
  f1 = f2
  f2 = tmp
End Sub
    • good
    • 0
この回答へのお礼

KenKen_SP さん、何度もありがとうございます。

試してみたいのですが、Functionってどうやってつかうんでしょうか?

お礼日時:2005/06/18 15:46

merlionXXさん、KenKen-SPさん、ちょっと、夕飯の支度の前に、作ってみました。

(^^;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim myColor As Variant
 Dim i As Long, f2 As String
 With Target
 If .Count > 1 Then Exit Sub
 If .FormatConditions.Count = 0 Then Exit Sub
  For i = .FormatConditions.Count To 1 Step -1
   If .FormatConditions(i).Type = 2 Then
    If Evaluate(.FormatConditions(i).Formula1) Then
     myColor = .FormatConditions(i).Font.ColorIndex
    End If
    Else
    With .FormatConditions(i)
    On Error Resume Next
    f2 = .Formula2
    If Err.Number > 0 Then
      f2 = Empty
      Err.Clear
    End If
    On Error GoTo 0
    If FormatCndOperand(.Operator, Target.Value, .Formula1, f2) Then
     myColor = .Font.ColorIndex
    End If
    End With
   End If
  Next
  If VarType(myColor) = vbLong Then
   MsgBox "ColorIndex: " & myColor
   Else
   MsgBox "自動=条件付で色が変わっていません。"
  End If
 End With
End Sub

Private Function FormatCndOperand(ByVal myType As Long, _
                 ByVal myValue As Variant, _
                 ByVal myF1 As Variant, _
                 ByVal myF2 As Variant) As Boolean
Dim myOperand As Variant
Dim myFormula As String
'Replace関数があるので、Excel2000 以上,その代用は、Application.Substitute
If Not (IsEmpty(myValue)) Then
 myOperand = Array("and(v1>= f1, v1 <= f2)", _
           "not(and(v1 >= f1,v1 <= f2))", _
           "v1 = f1", _
           "v1 <> f1", _
           "v1>f1", _
           "v1 < f1", _
           "v1>=f1", _
           "v1<=f1")
          
 myFormula = myOperand(myType - 1)
 myFormula = Replace(myFormula, "v1", myValue)
 myFormula = Replace(myFormula, "f1", myF1)
 If myF2 <> vbNullString Then
  myFormula = Replace(myFormula, "f2", myF2)
 End If
 FormatCndOperand = Evaluate(myFormula)
 Else
 FormatCndOperand = False
 End If
End Function

なお、パターンとフォントの区別をさせるコードは、大きくなりすぎますから、アドイン型の方がよいかもしれませんが、元の趣旨から離れてしまうような気がますので、ちょっと遠慮したいです。でも、いつも、merlionXXさんのスレッドは、暴走ぎみになりますね。迷惑じゃないかしら?(^^;
    • good
    • 0
この回答へのお礼

何度もありがとうございます。
Wendy02さんてほんと凄い人ですね。感心します。

書いていただいたコードも完璧に動きました。
ただ、あまりに複雑すぎて・・・・・。

No4で教えていただいたコートが「数式が」しか対応しないなら、条件付書式の方を「セルの値が」を「数式が」に変えてやればいいわけですよね?
「値」の定義?(等しい、間、以上、以下等)ってすべて数式で表せますよね?

お礼日時:2005/06/18 15:43

>基準外(答えが赤字で表示される)のセルを誤って選んでも、色が取得


>できればエラーロジックで排除できるので是非組み込みたかったのです。

本来のご質問とははずれるかもしれませんが、このような意図であれば、
条件付書式の色で分岐させるのではなく、値で条件分岐をさせた方が良い
と思います。

例えば、簡単な例ですが次のような感じです。

IF Target.Value < 101 Then Exit Sub


>数式でも値でも条件付書式のフォントの色をちゃんと取得できました。

うーーん。

当方は Windows98SE + Excel2002 の環境なのですが、エラー、、というか
誤まったカラーインデックスが返ります。

ひょっとして、私のやり方がマズイですか?

A1:条件付書式の設定

「セルが」「次の値と等しいとき」「5」

とし、A1 に 5 と入力すると 設定した文字色になり、ColorIndex:46 
と表示され、これは正しいのですが、A1 に 1 と入力しても
ColorIndex:46 と表示されてしまうのです。

原因としては、「セルが」の場合、

Evaluate(.FormatConditions(i).Formula1) は A1セル の入力値に関わら
ず 5 を返しますので、

If 5 Then ~

となるので評価は必ず True (または必ず False) となってしまう。

ですから、「セルが」の場合は、

IF Target.Value = .FormatConditions(i).Formula1 Then

のようにセルの値と比較する必要があると思います。

ここで、#6でコメントしたように、Operator プロパティーで指定された
比較方法で条件分岐させる必要性を感じました。

例えば、Operator プロパティーの値が xlBetween であったなら、少なく
とも Formula1 と Formula2 の2つのプロパティーを読みとらないと評価
できないですよね。

うーーん、、、

※質問者さまを差し置いて悩んでしまい、誠に申し訳ありません。
 ご迷惑なら、放置してください。

この回答への補足

> 例えば、簡単な例ですが次のような感じです。
> IF Target.Value < 101 Then Exit Sub

基準とする条件や値が一つや二つならそうしたいのですが、答えが出るセルは全部で25あり、それぞれ条件が違うんです。

ですからセルに条件付書式を組み込み、それが発動するかどうかを取得したいのです。

補足日時:2005/06/17 18:22
    • good
    • 0
この回答へのお礼

すみません、「セルの値が」でもOKというのは勘違いでした。
ご指摘ありがとうございました。

解決策は、「セルの値が」も数式化してしまうことかなあ・・・。

お礼日時:2005/06/17 18:16

KenKen_SPさんへ



>「数式が」に設定されているときには有効ですが、「セルが」の時
>にはエラーになりますね。

「セルが」にする場合は、本日中には無理だと思いますが、少し考えてみます。

この回答への補足

すみません、いろいろやってみましたら、条件が一つだけのときは「セルの値が」でもOKでしたが、複数になるとうまく働かないようです。

No6さん、ご指摘ありがとうございました。

補足日時:2005/06/17 18:05
    • good
    • 0

>条件付書式が「発動」したかどうかの判定も無理なのでしょうか?



#1-2 で不可能と書きましたが、、、いやいや、頭から決め付けるのは
良くないですね。不正確な情報で申し訳ありません。

Wendy02 さんが示されたコードのように FormatConditions から条件を
取得して、それを満たしているか調べれば不可能ではなさそうです。
ただ、

If Evaluate(.FormatConditions(i).Formula1) Then ~

は「数式が」に設定されているときには有効ですが、「セルが」の時
にはエラーになりますね。

そこで Wendy02 さんのコードを参考にして私もコードを書いてみたの
ですが、どうもうまくいきません。

条件を満たすか判定するには Type プロパティー で

 ・セルの値が
 ・数式が

のいずれかを取得し、それを Operator プロパティーの設定された次の
7 パターンのいづれかで判定しなければならないと思います。

 ・xlBetween
 ・xlEqual
 ・xlGreater
 ・xlGreaterEqual
 ・xlLess
 ・xlLessEqual
 ・xlNotBetween
 ・xlNotEqual

さらに、条件付書式は3つ設定できる、、、

コードを書いてて泣きが入りました(@@?)すみません。

今さらこんなこと言うのもなんなのですが、条件付書式で色が変更された
かどうが取得したとして、何に使うのでしょうか?
    • good
    • 0
この回答へのお礼

ありがとうございます。

> If Evaluate(.FormatConditions(i).Formula1) Then ~
> は「数式が」に設定されているときには有効ですが、「セルが」の時にはエラーになりますね。

わたしもFormulaとなっているので、そうじゃないかなと思ったのですが、試したらエラーになりませんでしたよ。
数式でも値でも条件付書式のフォントの色をちゃんと取得できました。

> 条件付書式で色が変更されたかどうが取得したとして、何に使うのでしょうか?

例で挙げた条件式は適当に並べただけですが、実際はさまざまな式により、答えが当方で定めた基準に適合するかどうかのチェックをしています。
基準内だった場合の答えのセルをクリックすると、その答えを導いたデータをコピーし、別シートに転記するマクロを書いてます。
その際、基準外(答えが赤字で表示される)のセルを誤って選んでも、色が取得できればエラーロジックで排除できるので是非組み込みたかったのです。

おかげさまで何とかなりそうです。

お礼日時:2005/06/17 16:50

merlionXXさんも、もうそろそろVBAを教える側に入るかもね。



今回のは、あまり高度とは言えないのですが、もし、今回のポイントはどこか、というなら、ここです。

○ If Evaluate(.FormatConditions(i).Formula1) Then

ここに気が付くなら、merlionXXさんは、もう、特に人に聞かなくても、本からでも、VBA掲示板を読んでいるだけでも吸収できると思います。

これは、Range オブジェクトの中に、条件付書式という部分は、FormatConditions というオブジェクト名に収まっています。FormatConditions 「s」がつくのは、いわゆる、コレクションです。コレクションの中に、式が入っていたら、その条件が成立するかどうかをみてあげます。

そのために、Evaluate があるのですが、独特の関数だと思います。ワークシートのあらゆる数式を、VBA上で実現させてしまうものです。WorksheetFunction では、限られてしまいます。

If VarType(myColor) = vbLong Then

次に、私自身、不安が残ったのですが、ある本で、ちょっと読んだことがあったのです。それは、私たちのPCは、32 bit ですから、数値の扱いは、そのままだとLong型になるそうです。null とお書きになっていたので、万が一にも、nullが入っても可能なように、myColor の変数を Variant 型に替えました。

Variant の変数に、数値が入れば、Long型にキャストされてしまう性質を使いました。つまり、何も入らないと、Empty になっていますから、Long型にはなりませんね。

後は、条件付書式って、条件1 > 条件2 >条件3 の順序になっていたと思います。仮に、条件3 で True が成立しても、その上の段階で、True になれば、その上の段階のIndexColor を優先させる、という考え方です。

何か変なところ、疑問がありましたから、ご面倒でもご指摘ください。
    • good
    • 0
この回答へのお礼

くわしくありがとうございました。

こんなヘンテコな質問にもちゃんとお応えいただきまして感謝感激です。

なぜ質問したかはNo6さんへのお礼に書きましたが、助かりました。
これからエラーロジックを作成します。

ありがとうございました。

お礼日時:2005/06/17 16:58

二箇所間違えました。

(^^;
最初に、Font のカラーを読み落とし、次に、条件付書式の条件の最初にTrueになったものを優先するのを忘れていました。

?If VarType(myColor) = vbLong Then
これは、これでよかったか、ちょっと自信がないので、うまくいかなかったら、ブレークポイントを取って、ちょっと変数の型をみてくださいね。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim myColor As Variant
 Dim i As Long
 With Target
 If .Count > 1 Then Exit Sub
 If .FormatConditions.Count = 0 Then Exit Sub
  For i = .FormatConditions.Count To 1 Step -1
   If Evaluate(.FormatConditions(i).Formula1) Then
    myColor = .FormatConditions(i).Font.ColorIndex
   End If
  Next
  If VarType(myColor) = vbLong Then
   MsgBox "ColorIndex: " & myColor
   Else
   MsgBox "自動=条件付で色が変わっていません。"
  End If
 End With
End Sub
    • good
    • 0
この回答へのお礼

Wendy02さん、ばっちりです!

ただ高度なVBAをいまいち理解できないんです。

> If Evaluate(.FormatConditions(i).Formula1) Then

> If VarType(myColor) = vbLong Then

この2つを解説してもらえるとうれしいのですが・・・。
わがまま言ってすみません。

お礼日時:2005/06/17 15:09

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q【Excel】 セルの色での判断はできますか?

使用環境:Office2003

条件付書式を使用して、セルの値がxxならばセルを着色する・フォントを変える、というのは可能ですが、

逆に

セルの色がxx(例えば赤等)ならば、隣のセルに1を代入する

ということは可能でしょうか?
※VBAを使わなければそれにこしたことはないですが、
 必要ならばコーディングも教えてください

よろしくお願いします。

Aベストアンサー

過去の質問を検索していただくと、たくさんの事例がありますが、結論から言うとVBAを使用しないとセルの色は取得できません

>セルの色がxx(例えば赤等)ならば、隣のセルに1を代入する
VBAを使用すればもちろんできますが、これは「例えば」の質問であって、このコードを書いても意味はないのでは?

ですから汎用的に使えるユーザ定義関数にしました。以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。
ワークシート画面に戻って、適当なセルに
=CellColor(A1)
と入力してみて下さい。A1セルの色番号が表示されます。(背景色なしの場合は0が返ります)
この戻り値判定して、関数などでセルに値をセットすればよいでしょう

Function CellColor(ByVal rng As Range)
With rng.Cells(1, 1).Interior
If .ColorIndex = xlNone Then
CellColor = 0
Else
CellColor = .ColorIndex
End If
End With
End Function

ただしセルの背景色を後から変えても、ユーザ定義関数の戻り値は自動的には変わりません。式を入力した後で背景色を変更した場合は
 ALT+Ctlr+F9
で強制再計算させる必要があります。

過去の質問を検索していただくと、たくさんの事例がありますが、結論から言うとVBAを使用しないとセルの色は取得できません

>セルの色がxx(例えば赤等)ならば、隣のセルに1を代入する
VBAを使用すればもちろんできますが、これは「例えば」の質問であって、このコードを書いても意味はないのでは?

ですから汎用的に使えるユーザ定義関数にしました。以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて...続きを読む

QExcel 書式を関数で判断。

Excelで、「もしA1が緑色ならば」などと、書式を関数で判断させるにはどうすればよいのでしょうか。

Aベストアンサー

#1の回答通り関数はありません。マクロでなら可能です。

ここでは「色の付いたセルを合計」という質問が結構出ています。
http://okwave.jp/kotaeru.php3?q=2000523

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

Q条件付書式で色づけしたセルの数をカウントしたい

WINDOWS XP EXCELL 2003です。
いつもお世話になります。

それそれのタイトル行に 7~556の行があり、
在庫警告  D列  条件付書式で =E7<BS7 で赤色に塗りつぶしに
在庫数   E列  =F7-G7+BR7
注文    F列  =SUMPRODUCT((H7:BQ7)*(MOD(COLUMN(H7:BQ7),2)=1))
出荷    G列  =SUMPRODUCT((H7:BQ7)*(MOD(COLUMN(H7:BQ7),2)=0))
適正在庫数 BS列 数値を入力

何かいい方法をと思い色々調べましたが私が必要としているものが中々見つかりません。
条件付書式で =E7<BS7 で試行錯誤しました。
例えば 条件付書式で =(E7<BS7=”1”,””) とかで1がでればとCOUNT関数でできないとか 全然駄目ですよね。

御指導を仰ぎたいのは

E列の条件付書式で赤と塗りつぶされたセルの数をカウント

ができたらと思いますが何かいい方法はありませんでしょうか。
可能ならよろしく御願いします。

WINDOWS XP EXCELL 2003です。
いつもお世話になります。

それそれのタイトル行に 7~556の行があり、
在庫警告  D列  条件付書式で =E7<BS7 で赤色に塗りつぶしに
在庫数   E列  =F7-G7+BR7
注文    F列  =SUMPRODUCT((H7:BQ7)*(MOD(COLUMN(H7:BQ7),2)=1))
出荷    G列  =SUMPRODUCT((H7:BQ7)*(MOD(COLUMN(H7:BQ7),2)=0))
適正在庫数 BS列 数値を入力

何かいい方法をと思い色々調べましたが私が必要としているものが中々見つかりません。
条件付書式で =E7<BS7...続きを読む

Aベストアンサー

条件付き書式の「結果を」拾い上げる方法はありません(マクロを使っても不可能です)が,ご質問でやりたい事は結局
>7~556の行があり、
>在庫警告  D列  条件付書式で =E7<BS7 で赤色に塗りつぶし

つまりこの行範囲で,E7<BS7 となっている行の個数を数えたいというお話に他なりません。

計算式:
=SUMPRODUCT((E7:E556<BS7:BS556)*1)


もちろんもっともっとシンプルイズザベストに,たとえばBT行に
BT7:
=IF(E7<BS7,1,0)
のように数式を記入し,
=SUM(BT7:BT556)
のように計算してしまえば十分OKです。

Qエクセル・・色の着いたセルの個数を数える関数はある?

エクセルの表内で塗りつぶしされているセルだけを数える様な関数はありますか?
セルには文字や数値は入力されておらず、塗りつぶしているだけです。
よろしくお願いいたします。

Aベストアンサー

こんにちは。

ユーザー定義関数で作ってみました。Ver4 マクロ関数で十分だと私は思いますが、Ver 4 マクロ関数ではケシカランというような方?は、以下のようなVBAでの解決方法が便利かなって思います。

VB Editor の標準モジュールに貼り付けてください。(挿入-標準モジュール)

なお、一般のブックの標準モジュールのユーザー定義関数に、Public キーワードをつけたところで、グローバル関数になるわけではありませんので、もしその点に不安のある方は、マニュアル等で、確認されたほうがよいかもしれませんね。

なお、以下は、引数のインデックスに0を入れると、配列出力するようにしてあります。

'------------------------------------
Function ColorCellCount(範囲 As Range, Optional インデックス As Integer = 1, Optional パターン As Integer = 0)
  Dim myRng As Range
  Dim myIndex As Integer
  Dim myPattern As Integer
  Dim myColor() As Integer
  Dim Ret() As Double
  Dim c As Range
  Dim i As Long
  Dim j As Long
  Set myRng = 範囲
  myIndex = インデックス
  myPattern = パターン
For Each c In myRng
   On Error Resume Next
   If myPattern = 0 Then
     i = WorksheetFunction.Match(c.Interior.ColorIndex, myColor, 0)
   Else
     i = WorksheetFunction.Match(c.Font.ColorIndex, myColor, 0)
   End If
   If i = 0 Then
     ReDim Preserve myColor(j)
     ReDim Preserve Ret(j)
      If myPattern = 0 Then
       myColor(j) = c.Interior.ColorIndex
      Else
       myColor(j) = c.Font.ColorIndex
      End If
      Ret(j) = 1
     j = j + 1
     On Error GoTo 0
     Else
      Ret(i - 1) = Ret(i - 1) + 1
   End If
  Next
  If myIndex <= 0 Then
   ColorCellCount = Ret()
  ElseIf myIndex > UBound(Ret) + 1 Then
   ColorCellCount = Ret(UBound(Ret()))
   Else
   ColorCellCount = Ret(myIndex - 1)
  End If
  Set myRng = Nothing
End Function

ワークシート上での使い方は、

A列
色付き
色なし
色なし
色付き
色付き

=ColorCellCount(A1:A5, 1 )

とすれば、上から数えて、1番目の色のセルの数が出ます。

=ColorCellCount(A1:A5, 2 )
を入れれば、色なしのセルの数が出ます。

=SUMPRODUCT(ColorCellCount(A1:A5, 0 ))

とすれば、全部の合計が出ます。

なお、
=ColorCellCount(A1:A5,1,1)

とすれば、文字の色を数えます。

こんにちは。

ユーザー定義関数で作ってみました。Ver4 マクロ関数で十分だと私は思いますが、Ver 4 マクロ関数ではケシカランというような方?は、以下のようなVBAでの解決方法が便利かなって思います。

VB Editor の標準モジュールに貼り付けてください。(挿入-標準モジュール)

なお、一般のブックの標準モジュールのユーザー定義関数に、Public キーワードをつけたところで、グローバル関数になるわけではありませんので、もしその点に不安のある方は、マニュアル等で、確認されたほうがよいかもし...続きを読む

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

Qexcel2010条件付き書式後にVBAにて処理

VBAビギナーのmaruです

Excel2010のワークシート上の範囲(Range(”E12:K120“))内の
各セルに条件付き書式(複数あり)にてフォント色を赤く(RGB 255,0,0)
変更したセル内のデータ(数字)のみ削除(””)するVBAを行いたいのですが

以下のコードでは、「セルの書式」で設定したフォント色で削除されてしまい
条件付き書式で変更された赤文字に反映されません
ご教授お願い致します


Private Sub CommandButton4_Click()

'データ入力セル内の赤文字の削除

Dim rd As Range

For Each rd In Range("E12:K120")

For i = 1 To rd.FormatConditions.Count

If rd.FormatConditions(i).Font.Color = RGB(255, 0, 0) Then

rd.Value = ""

End If

Next

Next


End Sub

以上、宜しくお願い致します

VBAビギナーのmaruです

Excel2010のワークシート上の範囲(Range(”E12:K120“))内の
各セルに条件付き書式(複数あり)にてフォント色を赤く(RGB 255,0,0)
変更したセル内のデータ(数字)のみ削除(””)するVBAを行いたいのですが

以下のコードでは、「セルの書式」で設定したフォント色で削除されてしまい
条件付き書式で変更された赤文字に反映されません
ご教授お願い致します


Private Sub CommandButton4_Click()

'データ入力セル内の赤文字の削除

Dim rd As Range

For Each rd In Range("E12:...続きを読む

Aベストアンサー

こんにちは!

横からお邪魔します。
VBAで条件付き書式のフォント色を調べる → 指定色の場合データを消去
という操作は
条件付き書式の条件を当てはめて、該当する場合にデータ消去!
という方法が一般的だと思いますが、

Excel2010からは
「DisplayFormatオブジェクト 」
というものが追加されているようですので、
見た目のフォント色(セル色)で区別ができるようです。

Sub Sample1()
Dim c As Range
For Each c In Range("E12:K120")
If c.DisplayFormat.Font.Color = RGB(255, 0, 0) Then
c.ClearContents
End If
Next c
End Sub

としてみてはどうでしょうか?m(_ _)m

Q【Excel】 色の一致するセル数をカウントしたい。

こんにちは。

ある条件にあてはまるセルに、書式でセルの色を設定してあります。
いくつかの条件で色分けしてあるのですが、これらのセルの色が「赤」は幾つ、「青」は幾つというふうに、
カウントすることは出来るのでしょうか。

よろしくお願いいたします。

Aベストアンサー

繰り返し何度も出てくる質問です。関数でと考えているなら、直接はできません。
VBAで中身たった1行のユーザー関数を作ればできます。
http://hp.vector.co.jp/authors/VA016119/hajimete/udf1.html
など
Googleで「エクセル セル 色 カウント」などで照会すると似た質問が出る。
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1225959
にはCELL関数のことも載っているが、旧いバージョンの関数なので
私は、除外して言ってます。

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

Q条件付書式設定でつけた色を残したまま条件式を解除する方法は?

エクセルで条件付書式設定を、条件に数式をいれて色付けしました。
そのセルの色表示を固定したいのですが、条件を削除してしまえば当然のことながら色表示までなくなります。

書式のコピーをしたら当然のことながら条件付書式設定まで一緒に引っ張って行ってしまいます。

なにかいい方法ご存知のかたいらっしゃいますか?

Aベストアンサー

こんばんは。Wendy02です。

このマクロは、条件付書式が、「数式が 」で設定されたものに限ります。そうでない場合、オプションを作らなくてはなりません。色は、フォントとパターンのみに対応しています。

'なるべく、標準モジュールに設定してください。
Sub FindFCondition2Format()
'条件付書式の色設定を、一般書式の色に換える
 Dim r As Range
 Dim c As Range
 Dim i As Integer
 Dim fc As Object
 On Error GoTo ErrHandler
 Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
 Application.ScreenUpdating = False
 For Each c In r
  'セルを動かさないと、数式が取れない
  c.Activate
  Set fc = c.FormatConditions
  For i = 1 To fc.Count
   '判定
   On Error Resume Next
   If Evaluate(fc(i).Formula1) Then
    c.Font.Color = fc(i).Font.Color
    c.Interior.ColorIndex = fc(i).Interior.ColorIndex
   End If
    On Error GoTo 0
  Next i
  c.FormatConditions.Delete
 Next c
 Set r = Nothing
 Application.ScreenUpdating = True
 Exit Sub
ErrHandler:
 MsgBox "条件付書式が見つかりません。", vbInformation
End Sub

こんばんは。Wendy02です。

このマクロは、条件付書式が、「数式が 」で設定されたものに限ります。そうでない場合、オプションを作らなくてはなりません。色は、フォントとパターンのみに対応しています。

'なるべく、標準モジュールに設定してください。
Sub FindFCondition2Format()
'条件付書式の色設定を、一般書式の色に換える
 Dim r As Range
 Dim c As Range
 Dim i As Integer
 Dim fc As Object
 On Error GoTo ErrHandler
 Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTy...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング