|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Sub CellsCondition() ' 条件付書式クリア Set w = Sheets("11月") 'w.Cells.FormatConditions.Delete w.Columns("J").FormatConditions.Delete ' 最終行取得 'b = w.UsedRange.Rows.Count b = 324 For r = 2 To b For c = 10 To 10 ' J列のみ ' A列の値(Weekday)が1なら赤 'Set f = w.Cells(r, c).FormatConditions.Add(xlExpression, xlEqual, "=Weekday(A" & r & ")=1") 'f.Interior.Color = RGB(255, 200, 200) 'f.StopIfTrue = False ' A列の値(Weekday)が7なら青(複数条件) 'Set f = w.Cells(r, c).FormatConditions.Add(xlExpression, xlEqual, "=AND(B" & r & "=0,Weekday(A" & r & ")=7)") 'f.Interior.Color = RGB(200, 200, 255) 'f.StopIfTrue = False ' 重複チェック＋特定の単語を除外 'Set f = w.Cells(r, c).FormatConditions.Add(xlExpression, xlEqual, "=AND(COUNTIF(J2:J324,J" & r & ")>1,J" & r & "<>""ｼｺﾐ"")") 'f.Interior.Color = RGB(255, 0, 0) 'f.StopIfTrue = False ' A列の値が1なら書式設定 'Set f = w.Cells(r, 1).FormatConditions.Add(xlExpression, xlEqual, "=Day(A" & r & ")=1") 'f.NumberFormat = "mm/dd(aaa)" 'f.StopIfTrue = False Next c Next r End Sub |