エクセルでの書式の参照

質問

エクセルで複数のシート７０枚（Ａ，Ｂ，Ｃ，Ｄ.....）ほどを使っています。Ｂ以降のセルはＡのセルを参照しているのですが、値は参照されますがパターンが参照されません。Ａのセルの色を変えたら自動的にＢ以降のセルにも反映されるようにしたいのですが、そういうことはできるのでしょうか？
仕事で使っていますが、今は１枚ずつ手動で色を変えていて、かなり手間がかかってしまいます。
マクロとかは初心者ですが、どなたか教えていただけないでしょうか？

例）
Ａ1の数値と色を変更したとき、Ｂ1，Ｃ1，Ｄ1・・・の数値と色も同じにしたい

回答No.6

Wendy02 · ベストアンサー

こんにちは。Wendy02 です。

今まで、せっかくお話が続いてきたのに、深く考えずに、割り込んでしまったので、状況をよく把握していないまま、うまく説明できていないかもしれません。以下は、日付をたぶん横に使用しているものとして考えられています。

＞=IF(WEEKDAY(F5)=1,"日",IF(WEEKDAY(F5)=2,"月"・・・,"土"))))))と入っているのですが、消す方法はないでしょうか。あと、条件付書式で日付のところはグレーにできたのですが、その下の曜日のところも条件付で色を変えられるのでしょうか？

まず、IF(WEEKDAY(F5)=1,"日"...
という長い式は、

=TEXT(F5,"aaa")

これだけで済むはずです。

＞空白になった日付の下の曜日のセルに"###"と入ってしまい、それを消す方法がわからなくて困っています。

もし、日付式のほうから空白値にするのでしたら、
表を横に使っているとすれば、おそらく、

2日目を
= F5+1
というような式でしょうか？

AF5:
27日目ごろのセルから、　（正確には28日かな？）

=IF(AE5="","",IF(MONTH($F$5)<>MONTH(AE5+1),"",AE5+1))

つまり、同じ原理で、曜日側は

=IF(AE6="","",IF(MONTH(AE5)<>MONTH(AE5+1),"",TEXT(AF5,"aaa")))

以下は、曜日がグレーにするのでしたら、
2日に対応する曜日の部分に、日付式に対して、条件付書式を作ります。
　=MONTH($F$5)<>MONTH(G5)　.....表が横の場合
として、書式コピーすればよいのですが……。

本来、これに土曜の青色と日曜日と休日の赤の条件付書式を加えなくてはならないのではありませんか？なかなか、これがややこしいです。

ちなみに、今回は、マクロは、あまり考えなくてよいと思いますが、
＞エラー９っていうのがでてしまったんです・・・
というのは、「シート名が違います」というエラーです。

　wh.Range("A1")

の部分は、変更はしません。約束事のようなものです。
使用しているシートの範囲のペーストする時の起点は、いつもA1 に決まっているまですね。

お礼

曜日のところも消えました！マクロのほうももう一度試したところうまくいきました。
私よりさらにエクセルにうとい上司にこの仕事をまかせるので、全ページ選択しているうちに他の作業をしてと収拾のつかない失敗をされてしまう可能性も考えると、ボタンひとつで済むマクロのままでいこうかと思います。

これで無事引継ぎできそうです。ありがとうございました。

あと投稿をするのが初めてだったため、要領をえない質問になっていたことをお詫びします。。。

回答No.5

Wendy02

こんばんは。
書いてから後、以下の方法でやってみたらやはり出来ましたので、#4の発言を修正します。

シートAをコピーしたい範囲を、「ほうきのアイコン」で書式コピーします。

次に、シートBを選んで、シフトを押しながら自分に必要な最後のシートを選択します。
そうすると、複数のシートタブが白くなるはずです。

そうしたら、シートBのA1 をクリックします。

そうすると、タブが白くなっているシート全部の書式が変更されています。
これは、条件付書式でも可能です。

お礼

難しい設定をしなくても、一度に書式もコピーできるのですね。勉強になります、ありがとうございました。今まで回答いただいているものを参考に修正しているのですが、３１日の表示を空白にはできるのですが、空白になった日付の下の曜日のセルに"###"と入ってしまい、それを消す方法がわからなくて困っています。=IF(WEEKDAY(F5)=1,"日",IF(WEEKDAY(F5)=2,"月"・・・,"土"))))))と入っているのですが、消す方法はないでしょうか。あと、条件付書式で日付のところはグレーにできたのですが、その下の曜日のところも条件付で色を変えられるのでしょうか？

ちなみに、#４のマクロで、"Ａ"は元になるシートの名前をいれればいいんですよね？"Ａ１"のところはなにをいれたらいいのでしょうか？"Ａ１"のまま実行してみたら　エラー９っていうのがでてしまったんです・・・

回答No.4

Wendy02

こんにちは。
細かい部分を読んでいませんが、別に、マクロは必要がないと思います。

シートタブで、シフトを押しながら、左端(シートB？)から右端までクリックして、複数のシートを選択します。
そして、セルの色を変更すれば、同様に変更が掛かります。お話の様子では、条件付書式をしたほうがよいかとは思いますが、条件付書式も書式ですから、一度は、書式コピーしなければなりませんね。

マクロでは、以下のようにしたらよいと思います。以下の場合は、シートAの使用している範囲を元にして、後のワークシートは残らず、色を含めた書式を変えてしまいます。

エラー処理はされていませんが、たぶん、エラーはないはずです。

Sub SheetFormatCopy()
　Dim MotoSh As Worksheet
　Dim wh As Worksheet
　Set MotoSh = Worksheets("A") '元になるシートを設定
　MotoSh.UsedRange.Copy
　Application.ScreenUpdating = False
　For Each wh In Worksheets
　　If wh.Name <> MotoSh.Name Then
　　　wh.Range("A1").PasteSpecial Paste:=xlFormats
　　End If
　Next
　Application.ScreenUpdating = True
　Set MotoSh = Nothing
End Sub

全部のシートでないときは、途中で、XXまできたら、For を抜けるように以下のように書き換えます。

For Each wh In Worksheets
　　If wh.Name <> MotoSh.Name And wh.Name <> "XX" Then
　　　wh.Range("A1").PasteSpecial Paste:=xlFormats
　　ElseIf wh.Name = "XX" Then
　　　Exit For
　　End If
Next

回答No.3

imogasi

#2です。祝日や会社独自休日に色をつける方法
祝日と会社休日のテーブルを同一シートの目立たないところに作る（別シートに造ると、条件付き書式が、別シートは参照できませんとエラーになるようなので）
I1:J5に
2005/5/3　　　 1
2005/5/5 　　　　　1
2005/10/20 1
2005/12/23 1
2005/10/18　　　　１
（実際は1月１日から始まって、もっと多いですが、手を抜いています）
祝日と隣列に１を入れる。
セル範囲A2:A32を範囲指定して
条件付き書式の「式が」で
=OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7)
上記は＃２で述べた条件ですが、その下へ追加する。
=VLOOKUP(A2,$I$1:$J$5,2,FALSE)=1
適当な「パターン」の色を選び、OKをクリック。

お礼

なるほど、おっしゃる通りで３１日表示の問題は表示しないように設定するだけのことですね。単純な勉強不足です。会社は土日祝日関係ないのでVLOOKUPの方だけでよさそうでした。ありがとうございます。ただ、同じシートに休日を入力しないといけないとなると各シートにすべてコピーしなければならなくなるので、できれば当初の質問通りＢ以降のシートにもＡの書式が反映されるようになるとよいと思うのですが・・・

実はこの仕事を年配の上司に引き継ぐため、なるべく作業を簡単にしておきたいと思っているのです。ご丁寧に何度も教えていただいているのに恐縮ですがもしなにか方法ありましたら教えていただけませんでしょうか。

回答No.2

imogasi

＃１です。
早速補足ありがとうございます。
当初質問から想像していたことと、補足の内容は大きくずれていました。
出勤簿であることぐらい、はじめに出してはどうですか
（１）たとえばAシートが変更のきっかけボタンと月指定など
（２）他のB.C・・・は社員の個人別出勤簿
（３）日付と曜日欄があって、毎月変わる
（４）土日祝の日の行の一部には色を変える
（５）２９日以後は、各月の月末日に応じて、ありえない日は消すか
見えなくする
などが問題らしい。これらはVBAでも、関数でも対処できる時々質問に出るありふれた
問題です。
なによりも当初表を考えたときにそれぐらいは、各シートで自動化するように考えるものです。
各シートに本来入れる（たとえばテンプレートのようなものを造って、新が増えればそれをコピーして使うのが普通ではないでしょうか。
A1に2005/9/1
A2に=A1
A3に=IF(A2+1<DATE(YEAR(A1),MONTH(A1)+1,1),A2+1,"")
と入れてA32まで式を複写すればよい。
そして土日を赤をつけるなら
書式ー条件付書式-式が
=OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7)
パターンでセル色指定-OKで土日に色がつきます。
祝日の色づけも過去の質問にあったように思いますがとりあえず略。

回答No.1

imogasi

>Ｂ以降のセルはＡのセルを参照しているのですが
=sheet1!A1のような方式で参照しているのでしょうか。
そうであれば、これも関数式の１種です。

（１）関数式は自分のセル以外の、セルの「値」を使って、計算など加工をして「値」だけを返すものに、エクセルでは限定してあります。
ですから関数式で書式を持ってくることはできません。
（２）現在質問者がやっている手作業を別にすると、
（３）残りは条件付書式を各シートでそろえて設定するとか、そろえて設定したテンプレートのようなものでシートを増やすか
（４）VBAでプログラムを作るしかありません
７０枚もあるシートを対象にして、元シートが変わると、書式を変えるのは
コンピュタ処理負荷的に負担が大きいように思います。
どの程度のセル（数）の波及性（A->Bー＞C・・・）なのか、言及しないと
プログラムも組みにくいでしょう。日付だけを連動させている、や繰越残高やその他どんな項目ですか。

補足

さっそくのご回答ありがとうございます。
出勤簿をつくっているのですが、Ａのシートは参照用で社員の名前など入ってなく白紙で、１日のところに「2005/10/1」と入力すると日付と曜日が変わるようになっています。Ｂ以降のシートには名前が入っていて、参照元はすべてＡのシートで日付と曜日のみ=sheet1!A1で参照されています。
現在はＡのシートはボタンを押すとヘッダーがすべて変わって、「○月出勤簿」と変更されるように前任者のときからマクロがくんであります。
３１日ない月のときに、最後のセルに翌月の分の「１日」と入ってしまうので、そういう時に「１日」のセルとその曜日のセルをグレーにしたいんです。
なにかよい方法はありますでしょうか？