[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロでの条件付き書式の設定』（感Feel）
お世話になります
例えば セルA3に次ぎの様な条件付き書式を設定します セルの値 次の値の間以外 =IF(A$1="",0,A$1) =IF(A$2="",2000,A$2) 書式=赤の塗りつぶし
これをマクロ記録すると
Sub syosiki()
With Range("A3")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:="=IF(A$1="""",0,A$1)", Formula2:="=IF(A$2="""",2000,A$2)"
.FormatConditions(2).Interior.ColorIndex = 3
End With
End Sub
となり、計算式の部分
Formula1:="=IF(A$1="""",0,A$1)"の様にワークシート関数の書き方になります
(記録時にその様に入力しているので当然と言えば当然なのですが。。。)
これを
Dim a
If Range("A1") = "" Then
a = 0
Else
a = Range("A1").Value
End If
の様な書き方が出来ましたらお教え下さい
と言いますのは、上記の例ではセルA3に対する書式ですが 実際にはこのセルは事前に決まってなく変動し、マクロ内での条件で位置決定します。 条件としてはこのセルと同列の1行目の値と同列の2行目の値以外の場合に赤塗りつぶしとしたいのです。
書式設定したいセルが変数 s に入って計算部分の結果をそれぞれ a b だとして
aの計算部分
If Cells(1,s.Column).Value = "" Then
a = 0
Else
a = Cells(1,s.Column).Value
End If
bの計算部分
If Cells(2,s.Column).Value = "" Then
b = 2000
Else
b = Cells(2,s.Column).Value
End If
として a b の範囲外の場合に赤で塗りつぶしとしたいのです
宜しくお願いします
Windows 2007 Excel 2010です (書いている途中でプレビューすると各バージョンが入力出来なくなってしまいました。なのでこの下にunknown)
＜ 使用 Excel：unknown、使用 OS：unknown ＞
こんにちわ。
予めa,bを求めているなら、条件付き書式は以下ですね。
Formula1:="=" & a, Formula2:="=" & b
（sy） 2016/11/05(土) 10:32
syさん 返信有難う御座います Cells(1,s.Column).Value も Cells(2,s.Column).Value もワークシート上で値は変動します もしも両方とも空白だった場合 syさんに教えて頂いた方法で行うと 次の値の間以外 =0 , =2000 となってしまい Cells(1,s.Column)の値やCells(2,s.Column)の値を変えた時に対応出来ません
具体的な内容をお話ししますと [[20161029122059]] でマナさんから 文字 C の位置をFINDにて検索 検索結果を変数に格納する方法を教わりました 今回行いたいことは得られた変数の一つ下のセルに 同列１行目と同列2行目の値以外であった場合にセルに色を付ける事です 使用しているブックは80ページ程のシートがあり 各シートの C の位置はまちまちなので検索をループで回しています なので s.Column も各シートで違いますので条件式の中にA$1等に固定出来ません
Changeイベントで都度、判定させる事は可能なのかとは思いますが 条件付き書式に組み込む事は困難でしょうか？
各シートでs.Columnはまちまちですが、使用しているシートの C の位置が途中で変更になる事はありません （感Feel） 2016/11/05(土) 11:46
１）検索をかけ（何を検索かは？ですが）
２）最初に見つかったセルについて
３）一つ下のセル値が、同じ列の１行目と２行目のセルの範囲外ならば赤くする（範囲内なら？）
４）それを全てのシートで繰り返す。
ということでしょうか。
もし、そうであれば、使うのは条件付き書式ではない気がします。
（マナ） 2016/11/05(土) 15:05
マナさん
先日はお世話になりました
10月29日 16:25 にマナさんから
Sheets("Sheet2").Range("A1:E5")の範囲内で"りんご"を検索
検索結果を変数に格納する事でactivecellが検索範囲内に無くても(他シートでも）
検索可能な事を教わりました
その時の"りんご"は例えで実際に検索していたのは "C" の文字でした
(最初の例え通り"りんご"とすれば良かったのですがツイ実際の検索文字で書いてしましました。紛らわしかったです。ごめんなさい)
なので
＞１）検索をかけ（何を検索かは？ですが）
は今回は" C "の文字です。
＞２）最初に見つかったセルについて
は前回教わった事で解決です
＞３）一つ下のセル値が、同じ列の１行目と２行目のセルの範囲外ならば赤くする（範囲内なら？）
これを行おうとしています(範囲外です)
＞４）それを全てのシートで繰り返す。
そうです。１シート分のマクロを作成し
別マクロから呼び出してます
実際に使用しようとしているマクロをそのまま載せます
Sub 各シートループ()
Application.EnableEvents = False
Dim sh As Long
For sh = 5 To Sheets.Count
Sheets(sh).Activate
Call C位置検索と書式設定
Next sh
Application.EnableEvents = True
End Sub
Sub C位置検索と書式設定()
Dim ran As Range, c As Range, non As Range, cou As Long
cou = 0
For Each non In Range("C5:L20")
If non.Value = "C" Then
cou = cou + 1
End If
Next non
If cou > 1 Then Stop 'Cが2つ以上あれば中断
Set ran = Range("C5:L20")
Set c = ran.Find(What:="C", After:=ran(ran.Count), LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, MatchByte:=False, SearchFormat:=False)
c.Offset(1, 0).Resize(1, 30).FormatConditions.Delete
c.Offset(1, 0).Select 'マクロ後検索位置確認
'C の下に条件付き書式設定 Cから30列右にコピーここから
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="="""""
.FormatConditions(1).Interior.ColorIndex = -4142
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:="=IF(J$1="""",0,J$1)", Formula2:="=IF(J$2="""",2000,J$2)"
'この Formula1:="=IF(J$1="""",0,J$1)", Formula2:="=IF(J$2="""",2000,J$2) のＪ列が、必ずしもＪ列とは限らない
.FormatConditions(2).Interior.ColorIndex = 7
.Copy
Application.EnableEvents = False
.Resize(1, 30).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Cells(Selection.Row, 1).Interior.ColorIndex = 7
Application.EnableEvents = True
End With
'C の下に条件付き書式設定 Cから30列右にコピーここまで
End Sub
マクロの下から12行目に J$1 ,J$2 がシートによっては I$1 ,I$2 や K$1 ,K$2 だったりするので、柔軟な表記は出来ないものでしょうか？
（感Feel） 2016/11/05(土) 17:00
半平太さん 返信有難う御座います この"C"はキーになる文字で他の文字を検索。 そこから範囲指定しての条件付き書式は考えておりません 他の文字は有りませんが設定をやり直す事は有るかもしれません その時は設定していた書式を外して再設定になります
手作業で条件付き書式を行う場合 セルの値 次の間の値以外 〜〜〜 〜〜〜 と設定して行きます この〜〜〜には 数値だったり、今回の様に計算式だったりしますが 最終的にどのように入れれば良いのか自分でも分からなくなってきました 整理して考え直してみます 申し訳ありません。このスレは一旦閉じさせて下さい
あれれ？半平太さんからもらったレスが消えてる??
（感Feel） 2016/11/05(土) 17:55
あら、書いちゃったので、貼っておきます。
Sub Main()
Dim ws As Worksheet
Dim c As Range
For Each ws In ThisWorkbook.Worksheets
Set c = GetCell(ws, "C")
If Not c Is Nothing Then
SetFormatCondition c
End If
Next
End Sub
Function GetCell(ByVal ws As Worksheet, ByVal sKeyWord As String) As Range
With ws.UsedRange.Cells
Set GetCell = .Find(What:=sKeyWord, _
After:=.Item(.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
MatchByte:=False, _
SearchFormat:=False)
End With
End Function
Sub SetFormatCondition(ByVal Rng As Range)
Dim sMin As String
Dim sMax As String
With Rng.EntireColumn
sMin = Val(.Cells(1).Value)
sMax = IIf(IsEmpty(.Cells(2).Value), 2000, .Cells(2).Value)
End With
Rng.Worksheet.Cells.FormatConditions.Delete
Rng.Offset(1).FormatConditions.Add( _
Type:=xlCellValue, _
Operator:=xlNotBetween, _
Formula1:="=" & sMin, _
Formula2:="=" & sMax).Interior.ColorIndex = 7
End Sub
検索する文字がシートに2個以上ある場合は考慮してません。
どうしましょ？
（まっつわん） 2016/11/05(土) 18:22
＞ .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _ ＞ Formula1:="=IF(J$1="""",0,J$1)", Formula2:="=IF(J$2="""",2000,J$2)"
上のステートメントをこんな風に変更する
Dim Cond1 As String, Cond2
Cond1 = Selection.EntireColumn.Cells(1).Address(True, False, 1)
Cond2 = Selection.EntireColumn.Cells(2).Address(True, False, 1)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:="=IF(" & Cond1 & "="""",0," & Cond1 & ")", Formula2:="=IF(" & Cond2 & "="""",2000," & Cond2 & ")"
＞あれれ？半平太さんからもらったレスが消えてる??
すみません。コードを読まないで、思った事を即アップしてしまいました。 読んだら勘違いだったのに気付いて慌てて消してしまいました。m(__)m
（半平太） 2016/11/05(土) 18:22
＞Cells(1,s.Column)の値やCells(2,s.Column)の値を変えた時に対応出来ません
なぜ？
＞マクロ内での条件で位置決定します。
この条件が変わった時に再設定すれば良いだけなんじゃないんですか？
（sy） 2016/11/05(土) 19:45
RangeオブジェクトのAddressプロパティをご存じないかな。
（セルのアドレスね、A1とかA$1とか）
（ 佳 ） 2016/11/06(日) 09:33
レスを閉じさせて頂いた後 行いたい事、分からず質問したかった事を考え直してみました
条件付き書式を設定するセル
マクロで ワークシート上の"C"の文字の位置を検索(FIND)しその下(Offset(1,0))で分かる
条件付き書式設定
設定時のダイアログに
セルの値 次の間の値以外 〜〜〜 〜〜〜 とし
〜〜〜 の部分は数値ではなく計算式(参照式)としたい
なぜならば、参照は "C" の列の１、２行目のセルの数値であり
これらの数値はマクロではなくワークシート上で変えた時に反映する必要があるため
"C" 位置の1行目は 変数c の列の1行目なので Cells(1,c.Column) で指定できる。
2行目は Cells(2,c.Column)で同様に指定できる。
しかし、このままではマクロを走らせた時に Cells(1,c.Column) Cells(2,c.Column)のセルに入っている数値が
〜〜〜に入り、計算式として入ってくれない。
質問したかった内容はこの数値ではなく計算式 =IF(J$1="",0,J$1) の様なA1形式で
条件付き書式設定ダイアログに入れる方法でした
今最初から読み返してみると、なんとも要領を得ない質問の仕方でした
素早く返信頂いたsyさん、マクロを組んで頂いたまっつぁんさん申し訳ありませんでした
半平太さん、佳さん Addressプロパティですね。
考えが至らずArray関数でA,B,C,D〜を指定しCellsプロパティの列に対応させて。。。。。
等々回りくどい事を考えていましたので大変助かりました。有難う御座いました
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:="=IF(J$1="""",0,J$1)", Formula2:="=IF(J$2="""",2000,J$2)"
これを
.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:="=IF(" & Cells(1, c.Column).Address(True, False, 1) & "="""",0," & Cells(1, c.Column).Address(True, False, 1) & ")", _
Formula2:="=IF(" & Cells(2, c.Column).Address(True, False, 1) & "="""",2000," & Cells(2, c.Column).Address(True, False, 1) & ")"
の様に変更して解決しました
（感Feel） 2016/11/06(日) 15:04
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.