• 締切済み

エクセルで常に表示できる書式の設定方法(VBA)

同様の質問で条件付き書式で挑戦しておりましたが、解決に至らず、VBAで質問させていただきます。 C列に日付(ただし1日を6コマに分けてあるため、6行同一日が続く)、D列に曜日が入っています。 E列には1日のコマ数の1~6が順次入っており、F列には祝日名が入っています。 G列以降に今年度のデータを貼付け、来年度用に編集し直ししたいのですが、 今年度は土日を抜いた状態で作成したのですが、来年度は土曜日も入れたデータを作成しなければならなくなりました。 そこで毎週6行追加になるのですが、データがAJ列まで入っており、ぱっと見て土曜日・祝日が判るようにしたいのです。 条件付き書式では、今年度のデータを貼付けるまでは良かったのですが、6行挿入するたびに書式がずれて行ってしまい、うまくいきませんでした。 VBAでは[Private Sub Worksheet_SelectionChange(ByVal Target As Range)]を使うのかな?程度しか判りません。 丸投げ状態で申し訳ありませんが、急ぎになってしまったため、よろしくお願いいたします。 当方Excel2002/Windows XPを使用。ウィンドウ分割も使用しております。

みんなの回答

noname#262398
noname#262398
回答No.6

#5のNNAQです。 質問欄の内容と#1の問答から条件付き書式についてのご質問かと思っていましたが、「本題」があったのですね。 以前のご質問も拝見して、ようやく分かりました。 今年度の表を元に、土曜日分を追加した来年度の表を作る、 というのが「本題」ですね? 結論を先に申し上げると、作成方法としてセルの挿入は最善策では無かったと思います。 条件付き書式のことより、なにより面倒だからね。 ずれてもずれたまま作って、後で一発で設定し直せますけど。 セルの挿入を使わない作成方法の一案で、 A列に週数・B列に日数・E列にはコマ数が入っているなら、それを元にID番号を作ります。 =TEXT(A1,"00")&B1&E1 1列挿入して仮にG列をID用にします(データ範囲はH列以降にずらす)。 今年度・来年度ともに同じようにします。 今年度の表をSheet1,来年度の表をSheet2として Sheet2 H1=IF(VLOOKUP($G1,Sheet1!$G:$AK,COLUMN(B1),FALSE)="",NA(),VLOOKUP($G1,Sheet1!$G:$AK,COLUMN(B1),FALSE)) 右へ下へコピー。 [形式を選択して貼り付け]-[値]。 土曜日と空白セルはエラーになるので[編集]-[ジャンプ]-[セル選択]で エラー値をDelete。 ID用のG列が不要なら削除。 VBAを使わなくても、それほど手間では無いと思いますが。 VBAだとこんな感じでしょうか。 Sub test1() '転記 Dim i As Long Application.ScreenUpdating = False For i = 0 To 52 Worksheets("Sheet2").Cells(i * 36 + 1, "g").Resize(30, 30).Value _ = Worksheets("Sheet1").Cells(i * 30 + 1, "g").Resize(30, 30).Value Next i Application.ScreenUpdating = True End Sub 単純に、30行転記して、次の30行は6行空けて・・・を繰り返してるだけです。 ついでに色付け Sub test2() '塗りつぶし Dim i As Long For i = 1 To Range("c65536").End(xlUp).Row If IsEmpty(Cells(i, "f")) Then If Weekday(Cells(i, 3).Value) = 7 Then Range(Cells(i, 1), Cells(i, "aj")).Interior.ColorIndex = 34 End If Else: Range(Cells(i, 1), Cells(i, "aj")).Interior.ColorIndex = 38 End If Next i End Sub 祝日はローズ、土曜は薄い水色です。 これもVBAを使わなくても、オートフィルタで手間がかからず色を付けられると思いますが。

noname#61366
質問者

お礼

過去の質問へリンクを張っていないのに、見てくださってありがとうございます。 そうなんです、地道に6行挿入は面倒です。でも別の方法なんて思いつきませんでした。 vlookupはよく使いますし、貼付け方法も色々やっています。 なのに、ここで使おうという発想はありませんでした。目からウロです。 今年度は週数がいい加減に振られているので、その辺の調整をした上で、アドバイスいただいた方法で試してみます。 ありがとうございました。

noname#61366
質問者

補足

今年度のデータを見返して、がっかりしました。 予定の項目自体が5個あり、それがそれぞれの列に入っているのですが... この5項目のために、それぞれ日付欄があり、一部は土曜日の予定が入っていたり、スタートの月日がずれていたりで、計算式を考え直すのが嫌なので、 今回は地道に、今年度の(月)~(金)の予定を日付をチェックしながら"値"を貼付けていきます。 こうすれば、書式がずれていくこともないし... お礼の欄ではありませんが、皆さまにお礼を申し上げます。 ありがとうございました。

noname#262398
noname#262398
回答No.5

横から失礼します。 条件付き書式は行を挿入してもずれません。 おそらくセルの挿入をしたからではないでしょうか。 行の挿入は、A列の左側の行番号をクリックして、行全体が選択されたら 右クリックメニューから[挿入]を選択。 10行目の下に6行挿入するなら、行番号11~16をドラッグして同じように。 [セルの挿入]で[行全体]を選択しても同じですが、[下方向にシフト]を選択すると、条件付き書式は、ずれてしまうようです。 条件付き書式を設定しなおすのは簡単です。 A1セルをアクティブにして[Ctrl]+[*]または[Ctrl]+[Shift]+[:], これで表全体を選択できるので、その状態で条件付き書式を設定するだけです。 フィルドラッグやコピー貼り付けの必要はありません。 単に特定の行に色をつけるだけなら、条件付き書式を設定しなくても、 オートフィルタを設定して、 D列で土曜日を抽出→塗りつぶし F列で祝日を抽出→塗りつぶし という作業だけで済むと思いますが、いかがでしょうか。 蛇足ですが、6行ずつ挿入するより、 C1セルに 2007/1/1 C2=IF(MOD(ROW(A2),6)=1,C1+1+(WEEKDAY(C1)=7),C1) と、数式を入れて下へコピーすれば、日曜を除いた1日6行の時間割(?)が作れます。

noname#61366
質問者

お礼

ご指摘の通り、私がしていたのは「セルの挿入」でした。 これでは皆さまに真意が伝わりませんよね(^^;) 「蛇足」部分ですが、来年度の日付はすでに入っておりますが、簡単に日付欄を作成する方法を教えてくださり、ありがとうございました。もう少しややこしい方法で入れていました。 さて、本題はその横の列に、今年度の土曜日の入っていない予定(1週間が30行)を貼付け、来年度用に編集したく、予定の列に6行分のセルを挿入しているのです。 お答えを待ちつつ、ウィンドウ分割のみで挿入していたら、やっぱり5週目でずれが生じていました。やりなおしです(T.T) VBA側からの回答をいただけないのは、VBAでもできないのでしょうか?>>皆さま

回答No.4

1行1行手直しするイメージをお持ちであれば違いますよ。 たとえば条件付き書式を設定されている領域を選んで、1回修正するだけです。 もしくは、1か所直すと全体に適用しますかと訊いてくるはずですので、 適用させれば良いです。 ちなみに、この程度の引用はOKです。

回答No.3

既に土曜の行は挿入済み、で条件付き書式の条件から判断すると祝日の判定がうまくできないということではないかと推測します。間違っていたらご指摘ください。 再度条件付き書式を以下のように設定してみてはいかがでしょう。 =「or(weekday($C1)=7,$F1<>””)」 「<>””」の代わりにisblank関数も使えますがこれはお急ぎでないときに。 当然がら祝日データは同一日の行には6個設定する必要があります。 冒頭記載の設定条件が違っていればこの条件付き書式の考え方は無効になりますし、ちょっと単純すぎるので不安ではありますが、どうでしょう。 なお、条件付き書式がずれるというのは行を挿入したために起きていると思いますので、再度設定しなおすことでクリアできるはずです。 ちなみに老婆心ながら、日曜日も加えておいて使わないなら非表示にするなどしておいた方がいいのではないかと...。

noname#61366
質問者

お礼

すみません、引用してしまいますが、 >条件付き書式がずれるというのは行を挿入したために起きていると思いますので、再度設定しなおすことでクリアできるはずです。 と、ありますので、条件付き書式ではダメなようですね。 編集しやすくするために、色を付けたかっただけですので、修正しながらでは意味がないのです。 せっかくご回答をいただいておりながら、申し訳ございません。 何度もありがとうございました。

回答No.2

いただいた材料で当方も考えてみます。 アンサーバックは質問自体を回答に「>」付などで記載されると長ったらしくなるのでそれを避けるためでした。独りよがりの判り辛い表現ですみませんでした。 9.は、データをやり取りすることが必要になるかな?とも思いましたのでyahooブリーフケースのようなものをお使いになれるかを伺ったものです。 サイト上での回答で済めば不要のものです。混乱させてしまい失礼をいたしました。 取り急ぎ

回答No.1

何点か確認をさせて下さい。 1.条件付き書式で可能ならそれでもOKか? 2.問題は条件付き書式を設定している表を作り変えようとされていて   6行挿入するたびにずれてしまうという点だけか? 3.条件付き書式で設定している条件と書式はどのような内容か? 4.AJ列とはA~J列のことか? 5.だとしてA~J列に入っているデータは、固定データか、それともどこかを参照しているデータか? 6.手作業で土曜日の行を既に入力されているか? 7.日付は日付データとして入力されているか? 8.曜日はどのように入力されているか? 9.データをやり取りするのに都合のよい方法はお持ちか? 以上ですが、それぞれ質問のアンサーバックは不要で、 ご回答だけで結構です。

noname#61366
質問者

補足

ありがとうございます。 1.それでもOKです。 2.はい、それが365日分ありますので、回数が多いと大変なのです。 3.条件は「数式で」「=weekday($C1)=7」で書式は「背景を水色に」です。 4.Z列の後、AAと続いて出てくるAJ列です。 5.現在のところ、固定データです。 6.C列にはすでに入っています。 7.はい 8.C列の日付を参照して、表示しているだけなので、C列の日付を利用で問題ありません。来年度以降も使用しますので... 9.??? すみません、おっしゃる意味がわかりませんが... アンサーバックの意味もわかりませんので、答えるべきと思うとおりにお答えいたしました。 よろしくお願いいたします。

関連するQ&A