回答編集履歴
3
定数・変数の整理
test
CHANGED
|
@@ -9,6 +9,8 @@
|
|
|
9
9
|
'A列の日付が土日なら着色する
|
|
10
10
|
Sub setInteriorColorByDate()
|
|
11
11
|
Const firstRow As Long = 2 '日付の開始行
|
|
12
|
+
Const colorColored As String = "&HF7EBDD"
|
|
13
|
+
Const colorWhite As String = "&HFFFFFF"
|
|
12
14
|
Dim sheet As Worksheet
|
|
13
15
|
Set sheet = ThisWorkbook.Worksheets(1)
|
|
14
16
|
|
|
@@ -19,15 +21,15 @@
|
|
|
19
21
|
values = .Range("A" & firstRow & ":A" & lastRow).Value
|
|
20
22
|
Dim i As Long
|
|
21
23
|
Dim dateValue As Date
|
|
22
|
-
Dim interiorColor As
|
|
24
|
+
Dim interiorColor As String
|
|
23
25
|
For i = 1 To UBound(values, 1)
|
|
24
|
-
'Debug.Print Hex(.Range("A" & (i - 1 + firstRow)).Interior.
|
|
26
|
+
'Debug.Print Hex(.Range("A" & (i - 1 + firstRow)).Interior.color)
|
|
25
27
|
dateValue = values(i, 1)
|
|
26
28
|
'日付が土曜、日曜なら背景色を着色
|
|
27
29
|
If Weekday(dateValue) Mod 6 = 1 Then
|
|
28
|
-
interiorColor =
|
|
30
|
+
interiorColor = colorColored
|
|
29
31
|
Else
|
|
30
|
-
interiorColor =
|
|
32
|
+
interiorColor = colorWhite
|
|
31
33
|
End If
|
|
32
34
|
.Range("A" & (i - 1 + firstRow)).Interior.color = interiorColor
|
|
33
35
|
Next i
|
2
変数名、重複コードを整理
test
CHANGED
|
@@ -9,7 +9,8 @@
|
|
|
9
9
|
'A列の日付が土日なら着色する
|
|
10
10
|
Sub setInteriorColorByDate()
|
|
11
11
|
Const firstRow As Long = 2 '日付の開始行
|
|
12
|
+
Dim sheet As Worksheet
|
|
12
|
-
|
|
13
|
+
Set sheet = ThisWorkbook.Worksheets(1)
|
|
13
14
|
|
|
14
15
|
With sheet
|
|
15
16
|
Dim lastRow As Long
|
|
@@ -17,16 +18,18 @@
|
|
|
17
18
|
Dim values As Variant
|
|
18
19
|
values = .Range("A" & firstRow & ":A" & lastRow).Value
|
|
19
20
|
Dim i As Long
|
|
20
|
-
Dim d As Date
|
|
21
|
+
Dim dateValue As Date
|
|
22
|
+
Dim interiorColor As Variant
|
|
21
23
|
For i = 1 To UBound(values, 1)
|
|
22
24
|
'Debug.Print Hex(.Range("A" & (i - 1 + firstRow)).Interior.Color)
|
|
23
|
-
d = values(i, 1)
|
|
25
|
+
dateValue = values(i, 1)
|
|
24
26
|
'日付が土曜、日曜なら背景色を着色
|
|
25
|
-
If Weekday(d) Mod 6 = 1 Then
|
|
27
|
+
If Weekday(dateValue) Mod 6 = 1 Then
|
|
26
|
-
|
|
28
|
+
interiorColor = "&HF7EBDD"
|
|
27
29
|
Else
|
|
28
|
-
|
|
30
|
+
interiorColor = xlNone
|
|
29
31
|
End If
|
|
32
|
+
.Range("A" & (i - 1 + firstRow)).Interior.color = interiorColor
|
|
30
33
|
Next i
|
|
31
34
|
End With
|
|
32
35
|
End Sub
|
1
背景色の色を質問者様のご希望に合わせました。
test
CHANGED
|
@@ -23,7 +23,7 @@
|
|
|
23
23
|
d = values(i, 1)
|
|
24
24
|
'日付が土曜、日曜なら背景色を着色
|
|
25
25
|
If Weekday(d) Mod 6 = 1 Then
|
|
26
|
-
.Range("A" & (i - 1 + firstRow)).Interior.Color = "&HD
|
|
26
|
+
.Range("A" & (i - 1 + firstRow)).Interior.Color = "&HF7EBDD"
|
|
27
27
|
Else
|
|
28
28
|
.Range("A" & (i - 1 + firstRow)).Interior.Color = xlNone
|
|
29
29
|
End If
|