エクセル操作について(VBA)

質問

こんばんは。いつもお世話になってます。

エクセルの操作ですが、
シート1に
A1セル　8:10
B1セル　12：05
C1セル　235(B1-A1の分換算）

シート2に
A1　7：01
B1　7:02
C1 7:03
D1 7:04
…　　…
○1 8:10
…　…
△1 12:05

と入力されているとして、
○2から△2まで(236セル)、自動的に色がつくようにしたいのですが、
関数や条件付き書式等で対応できるでしょうか？
VBAならなんとかできそうですか？

よろしくお願いします。

回答No.6

tsubuyuki · ベストアンサー

> VBAの勉強からしたいと思うのですが、
> tsubuyukiさんはどのように身につけられましたか？
> やはり業務上でですか？

エクセルでの業務もありますが・・私はアクセスから入りました。
（今でもそうだったりしますが）ＷＥＢを見ながら他さまのコードを読んで応用して・・
を繰り返して使って、なんとなく使えるかも？くらいのものです。
なので、かなり強引なやり方をしていると自覚することも多いです（笑）

今でもここのようなＱＡサイトで、他さまの回答からも勉強させてもらう事も多いです。
自分に全く関係ない質問への回答でも、いわゆる「目が覚めるような」回答をされている方が
多くいらっしゃいますので、読んでいて感心することばかりです。

さて、本題。
今現在のコードで足りない部分は
１）スケジュールを入力したら直ちに反映されるようにしたい
　=>これは実はすぐにでも出来るような気がします。
　　「ワークシートの内容が変更されたら」動くように設定するといいかもです。
　　でも、これは全て出来上がってからでも多分良いと思います。
２）スケジュールを修正したらそれが反映されるようにしたい
　=>スケジュールを表す「四角」に名前を付けていますので
　　考えられる手段は二つ「四角の場所・大きさを変える」「四角を削除して書き直す」
　　このどちらか、という事になろうかと思います。
　　どちらがいいのか・・思案が必要ですね。
３）日付に対応するスケジュール連番が増えたときに、自動的に行を増やすようにしたい
　　例えば図で言うなら「１２／１の連番３」とか
　=>実はここで悩んでいて先に進めていなかったりします。
　　（で、文字だけレスを書いているのですけれど＾＾；）
　　挿入だけならさほど難しくないのですが、じゃぁ逆に不要部分を削除するには？
　　などなど、悩みは尽きないので・・なかなか思いつきません＾＾；；
４）日をまたぐときはどうするか？
　=>これも悩みの種です。
　　単純に「時間が24:00を過ぎたら翌日の０時以降に分割」としていいのかどうか・・
　　ここも「ひずみ」が出てくる可能性があるので、難しいですね。
５）色の設定を・・・
　=>一色じゃ味気ないですし、ランダムにするわけにもいきませんからね＾＾；

以上、今の悩みです。

この案件が「急ぎのものではない」のであれば、
これらを考えながら勉強していくと、かなり実力が付くと思いますよ。
（ちょっと偏ってしまうかもしれませんが・・＾＾；；；）
こういう「題材」を見つけて、その完成を目標にするのは実に良い事と思います。
資格取得を目指すのも有意義ですが、日々の仕事の中でエクセルを使っていて、
「ここ、省力化できないかなぁ？」と考えていくのもまた有意義です。
それを実現するために調べるのは更に有意義です。
それが必ず力になりますから。
資格を所持していても実務に応用できないのであれば、
仕事をする上では全く意味がありませんからね。

以上、本件に全く関係ない持論を展開して終了しちゃいます。
失礼しました。

お礼

日々勉強で、絶えず新しい知識を培って、悩んで実践していくことがいつの間にか自分にとっての力になっていくのですね。

この案件は急ぎ…だと思います。といっても３か月程の猶予はありますが。
私はAccessもほぼ素人同然ですので、tsubuyukiさんのレベルにはまだまだ時間がかかりそうです。
ですが、諦めずに取り組んでいこうと思います。
最初は自動ででもできないのではないかと思っていましたから、何とかできるかもと思えただけでも満足です。あとは自分で頑張るのみです！
tsubuyukiさんには度重なる質問に対しても確実に返答をいただき、大変ありがたく思っています。
何度も回答いただき本当にありがとうございました！

回答No.5

tsubuyuki

すいません、取り急ぎ・・・
仕事の合間にチョコチョコなのでなかなか進まなくて申し訳ないです。

Sub test1()
Dim SHStart As Range, SHEnd As Range
Dim SHWidth As Single, SHHeight As Single, SHTop As Single
Dim SCStart As Single, SCEnd As Single, SCWidth As Single
Dim SCName As String
Dim TagRow As Long, TagDay As Date, TagNo As Long

Columns("A:Z").ColumnWidth = 9 '列幅です。適宜。

TagDay = Cells(Selection.Row, 1).Value
TagNo = Cells(Selection.Row, 5).Value
'↓塗りつぶしたい方の「日付」が入っている範囲を指定
TagRow = Range("A11:A15").Find(What:=TagDay, LookAt:=xlPart).Offset(TagNo - 1, 0).Row

SCName = Format(TagDay, "yymmdd") & "_" & TagNo & "_" & Format(Cells(Selection.Row, 3), "hhnn")

Set SHStart = Cells(TagRow, 3): Set SHEnd = Cells(TagRow, 26)
SHWidth = SHEnd.Left + SHEnd.Width - SHStart.Left
SHHeight = SHEnd.Height: SHTop = SHStart.Top
SHMin = SHWidth / 1440

SCStart = Cells(Selection.Row, 2).Value * 1440 * SHMin + SHStart.Left
SCEnd = Cells(Selection.Row, 3).Value * 24
SCWidth = Cells(Selection.Row, 4).Value * SHWidth

ActiveSheet.Shapes.AddShape(msoShapeRectangle, SCStart, SHTop, SCWidth, SHHeight).Select
With Selection.ShapeRange
With .Fill
.ForeColor.RGB = RGB(255, 255, 255)　　'塗りつぶし色です。今は白
End With
With .Line
.ForeColor.RGB = RGB(0, 0, 0)　　'四角の枠線です。今は黒
.Weight = 0.5
End With
.Name = SCName
End With
End Sub

Ａ１：Ｅ７の範囲に、予定を打っています。
１０行目は０時台～２３時台を１セルずつ。
１１行目から下は「塗る日」ですね。
その中のＢ列は連番（１・２・・・増やしてください）で
これが上の表のＥ列に対応します。

上の表に日付・スタート・エンドを入力すると、
Ｄ列に分間（=C1-B1）を計算します、Ｅ列は手入力。
上の表のどこかの行にフォーカスを置き、上記マクロを走らせると、
該当の日の、該当の連番、該当する時間に四角を置いていきます。

このままでは到底使えないと思いますが、精査したらもしかしたら？
と言うくらいの、考え方の一案としてお納めくださいませ。

お礼

仕事の合間に回答ありがとうございます。
これです！こんな感じのやつ求めてました。
ただ、まだまだ追加したい機能も多々とありますし改変等も必要ですので、
VBAを難なく使えるようじゃないと厳しそうです。

まずはVBAの勉強からしたいと思うのですが、tsubuyukiさんはどのように身につけられましたか？
やはり業務上でですか？
私は業務上利用場面がありませんので、当面VBAエキスパートの取得を目指してみようかと思います。
回答いただいたVBAが難なく理解できるレベルまで到達し、早く思い通りの作業をできるようにしていきます。

回答No.4

tsubuyuki

投げっ放しもアレなので、連投をお許しください。

６行目は予定１に対応して赤塗り
　　条件付書式（式）：=AND(A$4>=$A$2,A$4<=$B$2)：赤塗り

７行目は予定２に対応して青塗り
　　条件付書式（式）：=AND(A$4>=$A$3,A$4<=$B$3)：青塗り

それぞれ別の式で条件付き書式を設定しています。

手動で設定していくとやはり少々面倒ですけどね。

お礼

回答ありがとうございます。
実は1行に1予定ではないのです。C列に指定行の数字を記入すれば何とかなるのかなと思うのですが…。
また
また各列は、何百・何千行にもなる予定で、それらを指定した行、指定した時間を塗れるようにしたいのです。
回答1の補足に例示していることをやりたいです。

回答No.3

tsubuyuki

失礼しました。
> ○2から△2まで(236セル)、自動的に色がつくようにしたい
書いてありましたね。

図をご覧いただいて・・面倒臭がりなもので、範囲は狭いですが・・
Ａ２セルに開始時刻、Ｂ２セルに終了時刻をセットしました。
４行目に時刻（７時から１分刻み）を用意してあります。
例では、この時刻の行の下２行（５行目と６行目）に条件付き書式を設定しています。

とりあえず、Ａ５セルに条件付き書式を
「数式を利用して云々」を選択、式を「=AND(A$4>=$A$2,A$4<=$B$2)」にしてやり、ＯＫ。
Ａ５セルを必要分だけ、コピーしてやってみてくださいませ。

おそらく、ご希望の色付けが出来るのではないでしょうか。

・・・と言うか、スケジュールが３件なら、塗る行も３行・・ってことですかね。
だとしたら、少々面倒かもしれませんが、これの応用で（手動で）出来ると思いますよ。

自動化なさりたいのであれば、ＶＢＡでの設定も可能とは思います。
これまたちょっと面倒かもしれませんけどね。

お礼

回答ありがとうございます。
私もわかりにく説明ですみませんでした。

回答No.2

keithin

２行目を行選択する
ご利用のエクセルのバージョンが不明なのでＥｘｃｅｌ２００７以降で、ホームタブの条件付き書式▼から新しいルールを開始
数式を使用して…を選び
=(COUNT(INDIRECT("Sheet1!A1:B1"))=2)*(MEDIAN(INDIRECT("Sheet1!A1"),INDIRECT("Sheet1!B1"),A1)=A1)
と記入、書式ボタンで色を塗って出来上がり。

＃ただし
シート２のＡ１以右の時刻を、オートフィルドラッグでだらっと作成すると上手くいかないので注意してください。
たとえばＡ１に
=TIME(7,COLUMN(),0)
右にコピー
などのようにしておくのは、安全な方法の一つです。

お礼

回答ありがとうございます。
試させていただいたのですが少しうまくいかないです。
A2,B2セル最初の2列のみ書式が設定され、該当セルは書式に変化がありません。
また、同じ行に追加が発生した場合は対応できるでしょうか。
もう少し補足をいただければと思います。

回答No.1

tsubuyuki

まぁ・・横にズラッと持つのもまた大変だとは思いますけど・・

とりあえず、Sheet2の１行目（時刻が入っている範囲）を選択し、
条件付書式
・指定のセルを含むセルだけを書式設定を選択
・「セルの値」「次の値の間」をそれぞれ選択
・「Sheet1!$A$1」と「Sheet1!$B$1」になるように指定
・お好きな書式を設定
で、ＶＢＡを使わなくても出来ますよ。
ちなみに、スタート・エンド、どちらも「込み」です。

どうしてもＶＢＡが良い、と言うときは補足くださいませ。

補足

回答ありがとうございます。
私の伝達ミスです。時刻が入っているセルではなく、その下の２行目・３行目を塗っていきたいのです。
また、sheet1のA列は日付、B列はスタート時刻、C列はエンド時刻、D列に塗装をしたい行を
それぞれ入力していくと、自動的に色が塗られるようにしたいのです。
たとえば…
sheet1
A1セル　12/17
B1セル　8:10
C1セル　12:05
D1セル　2（2行目のこと)

A2セル　12/17
B2セル　12:10
C2セル　14:05
D2セル　2

A3セル　12/17
B3セル　11:10
C3セル　13:05
D3セル　3

A4セル　12/17
B4セル　23:10
C4セル　2:05(翌日)
D4セル　5

sheet2は指定した行の、スタート時刻からエンド時刻までがすべて塗られているイメージです。
（あるいは全自動というわけでなく、マクロ利用など１動作で転記ができてもok）

といった感じです。

sheet2の一行目はあくまでも日時を入れるのみで、
塗りたいのは2行目から下です。
また、本当は一日ごとに表をつくりたいのですが、日のまたぎが複雑になる懸念から、ひとまず横並びでできるかどうかの検証から始めています。（もし塗ることができればコピーのマクロ等をつくればいいだけなので…）

可否だけでもご教授ください。
よろしくお願いします。