Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7108 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【41129】数式の入っているセル色変更 なーちん 06/7/31(月) 15:41 質問[未読]
【41133】Re:数式の入っているセル色変更 ぱっせんじゃー 06/7/31(月) 16:58 発言[未読]
【41134】Re:数式の入っているセル色変更 Jaka 06/7/31(月) 17:00 発言[未読]
【41135】Re:数式の入っているセル色変更 なーちん 06/7/31(月) 17:29 発言[未読]
【41136】Re:数式の入っているセル色変更 ぱっせんじゃー 06/7/31(月) 18:05 発言[未読]
【41137】Re:数式の入っているセル色変更 なーちん 06/7/31(月) 18:10 発言[未読]
【41138】Re:数式の入っているセル色変更 ぱっせんじゃー 06/7/31(月) 18:17 発言[未読]
【41139】Re:数式の入っているセル色変更 なーちん 06/7/31(月) 18:29 質問[未読]
【41160】Re:数式の入っているセル色変更 ichinose 06/8/1(火) 9:14 発言[未読]
【41162】Re:数式の入っているセル色変更 なーちん 06/8/1(火) 10:27 お礼[未読]
【41165】Re:数式の入っているセル色変更 ichinose 06/8/1(火) 12:25 発言[未読]
【41170】Re:数式の入っているセル色変更 なーちん 06/8/1(火) 14:35 お礼[未読]
【41161】Re:数式の入っているセル色変更 ｍ2ｍ10 06/8/1(火) 9:55 発言[未読]
【41163】Re:数式の入っているセル色変更 なーちん 06/8/1(火) 10:39 お礼[未読]
【41174】Re:数式の入っているセル色変更 ｍ2ｍ10 06/8/1(火) 14:45 発言[未読]
【41129】数式の入っているセル色変更
質問  なーちん  - 06/7/31(月) 15:41 -
引用なし
パスワード
   以前にもこちらで教えていただいたものです。
sheet1にランダムに数式が設定されています。

標準モジュールにて
Function siki(Aa)
siki = Aa.HasFormula
End Function

条件付書式にて
数式が=siki(A1)　→　書式：セル色変更

としております。(ぱっせんじゃーさんありがとうございます。)
上記内容でsheet1の数式の入っているセルの色変えが実現しています。

ここで、sheet2にて、sheet1の内容を参照しようとすると、
sheet1にsheet2で入力した。参照式が入力されてしまいます。
ex.
　　sheet2のセルD2に"=" sheet1の"E7"を参照してエンター
　　すると、sheet1のD2に、"=sheet1!F1"が入力されてしまう。????

どなたか回避策がわかるかたよろしくお願いいたします。
【41133】Re:数式の入っているセル色変更
発言  ぱっせんじゃー  - 06/7/31(月) 16:58 -
引用なし
パスワード
   シートがグループ化されているのでは？
とも思いましたが、"E7"と"F1"と違いますので、
それでもなさそうですね。
【41134】Re:数式の入っているセル色変更
発言  Jaka  - 06/7/31(月) 17:00 -
引用なし
パスワード
   ▼なーちん さん：
>ここで、sheet2にて、sheet1の内容を参照しようとすると、
>sheet1にsheet2で入力した。参照式が入力されてしまいます。
>ex.
>　　sheet2のセルD2に"=" sheet1の"E7"を参照してエンター
>　　すると、sheet1のD2に、"=sheet1!F1"が入力されてしまう。????
sheet2のセルD2に書いた実際の関数を書いてください。
また、sheet1の"E7"にどんなものが書いてあるのでしょうか？
もう少し詳しく書いてください。

今回は、
=siki(Sheet1!D2)
こんな感じのユーザー定義関数とは関係してないですよね？
【41135】Re:数式の入っているセル色変更
発言  なーちん  - 06/7/31(月) 17:29 -
引用なし
パスワード
   分かりづらくて申し訳ございません。
なにが起こっているのかわからないもので・・・

sheet1のE7には、文字列"aaa"が入っています。

sheet2のD2を選択　"="を入力
sheet1を選択
sheet1のE7を選択
エンター

通常、上記操作を行うと、sheet2のD2に式"=sheet1!E7"が設定されて
"aaa"が表示されると思うのですが、

先のマクロと条件付書式が設定されていると

同様の操作で、sheet1のD2に式"=sheet1!E4"が設定されるのです。
セル値はタイミングによって異なるようです。

環境は、winXPpro2002sp2　excel2002sp3です
【41136】Re:数式の入っているセル色変更
発言  ぱっせんじゃー  - 06/7/31(月) 18:05 -
引用なし
パスワード
   条件付き書式はどのセルに設定されている
のでしょう？
【41137】Re:数式の入っているセル色変更
発言  なーちん  - 06/7/31(月) 18:10 -
引用なし
パスワード
   ▼ぱっせんじゃー さん：
>条件付き書式はどのセルに設定されている
>のでしょう？
sheet1の全セルです。

ちなみに、直接"=sheet1!E7"と入力すれば
参照できます。
【41138】Re:数式の入っているセル色変更
発言  ぱっせんじゃー  - 06/7/31(月) 18:17 -
引用なし
パスワード
   確信はありませんが、
全セルに対して設定されているためかな、
とも思います。
A1〜IV65536ですよね？
同じブックでも、全セルに条件付き書式
が設定されているシートと、一つの設定
されていないシートでは、セルに入力後
エンターでセルに式あるいは文字列が反映
される時間が違いました。
【41139】Re:数式の入っているセル色変更
質問  なーちん  - 06/7/31(月) 18:29 -
引用なし
パスワード
   ▼ぱっせんじゃー さん：
>確信はありませんが、
>全セルに対して設定されているためかな、
>とも思います。
>A1〜IV65536ですよね？
>同じブックでも、全セルに条件付き書式
>が設定されているシートと、一つの設定
>されていないシートでは、セルに入力後
>エンターでセルに式あるいは文字列が反映
>される時間が違いました。

全セル対象をやめました。
記載範囲a5〜z156に条件付書式を設定しましたが
現象は変化ありません

ひょっとしたらこの現象がでるのは私だけでしょうか？
同様bookを他の端末にて確認しましたが同様でした。
さらに、新規bookを作成して先のマクロおよび条件付書式
だけにしてみましたが変化ありません
他の手段を考えたほうがよいのでしょうか？
【41160】Re:数式の入っているセル色変更
発言  ichinose  - 06/8/1(火) 9:14 -
引用なし
パスワード
   おはようございます。

現象をExcel2002で確認しました。

再現手順

・新規ブックの標準モジュールに

'============================================
Function siki(rng As Range) As Boolean
　　siki = rng.HasFormula
End Function

を記述します。


・Sheet1のシートにて、セルＡ１を選択します。

・選択した状態で「書式」----「条件付書式」とクリックして、
　「条件付き書式の設定」ダイアログを表示させます。

・ここで条件を設定します。「セルの値が」と表示されているコンボボックスを
　「数式が」に変更します。

・上記のコンボボックスのすぐ右隣にあるテキストボックスに
　「=siki(a1)」と入力します（両端の「」は除く）。

・書式ボタンをクリックして「パターン」タブで色として赤を選択して
　ＯＫボタンをクリックしてください。再度、「条件付き書式の設定」ダイアログに
　戻りますから、ＯＫボタンで設定を終了してください。

これで、準備完了です。

試しにSheet1のセルＡ１に「=a2」と入力してみてください。
セルＡ１が赤く塗りつぶされるはずです。


・Sheet1のセルＡ１に「aaa」と入力してください（両端の「」は除く）。
　塗りつぶしが消えますよね！！

・Sheet2のシートを選択してください。

・セルＤ２に　「=」（両端の「」は除く）を入力した状態
　（カーソルはセルＤ２内で点滅している状態）でSheet1のセルＡ１を
　選択します。

・Sheet1のセルＡ１は、破線で囲まれ、数式バーには、「=Sheet1!A1」と表示されています。

・このまま、確定のEnterキーを押してください。

この時の結果はまちまちですが、少なくともSheet2のセルＤ２には、
何も入力されていないという現象になりました。

代わりにSheet1のセルＤ２に　#REFになる場合もありましたし、

また、Sheet1のセルＡ１の結果を参照している場合もあります。
このときは、Sheet1のＤ２には「=Sheet1!A1」と入力されています。
　
ユーザー定義関数　siki　に何らかの問題がありそうですねえ！！


新規ブックの標準モジュールに

'===========================================================
Sub main()
　　Application.Names.Add "hformula", , , , , , , , , "=get.cell(48,rc)"
　　With Worksheets("sheet1").Range("a1:z100")
　　　 .FormatConditions.Delete
　　　 .FormatConditions.Add Type:=xlExpression, Formula1:="=hformula"
　　　 .FormatConditions(1).Interior.ColorIndex = 3
　　　 End With
End Sub


シートSheet1をアクティブにして、mainを実行してみてください。

上記のコードはSheet1のセルＡ１：Ｚ１００のセル範囲に対して、
数式が入っていれば、赤く塗りつぶす条件付き書式を設定しています。


これで同じようにSheet2のセルＤ２から「=」と入力して
Sheet1のセルA1を参照してみてください。

今度は、正しくSheet2のD2に数式が反映されるはずです。

試してみてください。
【41161】Re:数式の入っているセル色変更
発言  ｍ2ｍ10  - 06/8/1(火) 9:55 -
引用なし
パスワード
   こんちは

　例として。

　Selection.SpecialCells を利用する方法も有りかな？
　ActiveSheet.Unprotect　を入れてますので、マウスで範囲を
　ドラッグすればその範囲で、範囲指定が無ければ、シート全体
　になります。

Sub 数式の色付け()
On Error GoTo end00
Dim objra As Range
Dim objrb As Range
Dim Response
　
　 ActiveSheet.Unprotect
　 Set objra = Selection.SpecialCells(xlCellTypeFormulas) '計算式
　 For Each objrb In objra
　　　Range(objrb.Address).Select
　　　Selection.Font.ColorIndex = 3
　 Next
　 Exit Sub
end00:
MsgBox "数値DTが無い", vbCritical, ""
On Error GoTo 0
End Sub
【41162】Re:数式の入っているセル色変更
お礼  なーちん  - 06/8/1(火) 10:27 -
引用なし
パスワード
   ▼ichinose さん：
おはようございます。
丁寧な解説ありがとうございます。
下記内容で実現できました。

>>新規ブックの標準モジュールに
>
>'===========================================================
>Sub main()
>　　Application.Names.Add "hformula", , , , , , , , , "=get.cell(48,rc)"
>　　With Worksheets("sheet1").Range("a1:z100")
>　　　 .FormatConditions.Delete
>　　　 .FormatConditions.Add Type:=xlExpression, Formula1:="=hformula"
>　　　 .FormatConditions(1).Interior.ColorIndex = 3
>　　　 End With
>End Sub
>シートSheet1をアクティブにして、mainを実行してみてください。
>
>上記のコードはSheet1のセルＡ１：Ｚ１００のセル範囲に対して、
>数式が入っていれば、赤く塗りつぶす条件付き書式を設定しています。

withの中は条件付書式なのは分かるのですが
Application.Names.Add "hformula", , , , , , , , , "=get.cell(48,rc)"
が、なんのことやらさっぱりわかりません
hformulaっていう関数を定義してその内容が"=get.cell(48,rc)"ってこと？
helpをみても引けないのですが。。。
【41163】Re:数式の入っているセル色変更
お礼  なーちん  - 06/8/1(火) 10:39 -
引用なし
パスワード
   ▼ｍ2ｍ10 さん：
おはようございます。
ありがとうございます。
動的に色を変化させたいのです。

参考にさせていただきます。
【41165】Re:数式の入っているセル色変更
発言  ichinose  - 06/8/1(火) 12:25 -
引用なし
パスワード
   ▼なーちん さん：

>おはようございます。
>丁寧な解説ありがとうございます。
>下記内容で実現できました。
>withの中は条件付書式なのは分かるのですが
>Application.Names.Add "hformula", , , , , , , , , "=get.cell(48,rc)"

>が、なんのことやらさっぱりわかりません
>hformulaっていう関数を定義してその内容が"=get.cell(48,rc)"ってこと？
関数ではなく、あくまでも名前の定義です。
やってることはご推察とおり

「hformula」という名前で"=get.cell(48,rc)"を登録したのです。

「get.cell」は、Excel4Macroというものらしいです。
その昔は、よく使われていたみたいです。
（私もそのあたりの歴史はしりませんし、Excel4Macroだって、埋もれていた
Helpをたまたま見つけたのでそれを調べて今回も記述しました）

このExcel4Macroは、そのままセルの数式や条件付書式に直接指定できないみたいなので

名前の定義で登録した名前で指定しています。

因みに「GET.CELL()」は、セルに関する様々な情報が取得できます。
GET.CELL(48,rc)の　48　が数式か否かを返す式になっています。

このExcel4Macroを使うと、VBAのオブジェクトからでは、バグで正常に作動しない
内容でもExcel4Macroを使うと正しく答えを出してくれる場合もあります
（私が知っているの中では、シート内のページ数の取得ではExcel4Macroを使っています）


もっともExcel4Macroは、いつまでサポートされるかわかりませんけどね！！

尚、Excel4MacroのHelpに関しては、

http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=39734;id=excel

参考までに・・・。
【41170】Re:数式の入っているセル色変更
お礼  なーちん  - 06/8/1(火) 14:35 -
引用なし
パスワード
   ありがとうございます。

なかなか奥深いのですね。

今後ともよろしくお願いいたします。
【41174】Re:数式の入っているセル色変更
発言  ｍ2ｍ10  - 06/8/1(火) 14:45 -
引用なし
パスワード
   こんちは

Application.OnKey を使って文字に色を付ける、
一度[マクロ開始()]を動かし、改行で処理をする。

Sub 表示変更()
　　Dim C As Range

　　Set C = Range(ActiveCell.Address)
　　
　　If Mid(C.Formula, 1, 1) = "=" Then
　　　　C.Font.ColorIndex = 3
　　　Else
　　　　C.Font.ColorIndex = 1
　　End If
　 Application.SendKeys "{DOWN}"
　

End Sub

Sub マクロ開始()
　　　Application.OnKey "{ENTER}", "表示変更"
　　　Application.OnKey "~", "表示変更"
End Sub

Sub マクロ終了()
　　　Application.OnKey "{ENTER}"
　　　Application.OnKey "~"
End Sub
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7108 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free