Last active
October 24, 2017 11:44
-
-
Save Lycheejam/bb818c3c3cf03e332885580e6076567a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'************************ | |
'**定数 | |
'************************ | |
'シート名 | |
Public Const sheet_list = "抽出リスト" | |
'************************ | |
'**グルーピング処理 | |
'************************ | |
Sub rowGrouping() | |
Dim rowMax As Long 'レコード数 | |
Dim keyNew As String 'グルーピングキー(現在) | |
Dim keyOld As String 'グルーピングキー(現在-1) | |
Dim colorIdx As Long '色決め番号 | |
Dim targetSheet As Worksheet 'サブに渡すシートオブジェクト | |
Dim targetRange As Range 'グルーピング範囲 | |
Dim i As Long '汎用カウンタ | |
Set targetSheet = ThisWorkbook.Worksheets(sheet_list) | |
'最大行数取得 | |
rowMax = targetSheet.Cells(Rows.Count, "A").End(xlUp).Row | |
colorIdx = 0 '初期化 | |
For i = 2 To colMax '1行目は項目名の為、2行目からスタート | |
keyNew = targetSheet.Cells(i, "A").Value 'キー取得 | |
Set targetRange("A" & i & ":X" & i) '着色範囲設定 | |
If keyNew = keyOld Then 'キーが1つ前と同じならば | |
call colorSw(targetRange, colorIdx Mod 2) '前と同じ色を着色 | |
Else 'キーが1つ前と違うならば | |
colorIdx = colorIdx + 1 'キーが違うので色を変える | |
call colorSw(targetRange, colorIdx Mod 2) | |
End If | |
keyOld = keyNew 'OLDキーにNEWキーを格納 | |
Next i | |
'オブジェクト解放 | |
Set targetSheet = Nothing | |
Set targetRange = Nothing | |
End Sub |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'************************ | |
'**CelleColor設定 | |
'************************ | |
' colorSw(色設定先シート&範囲, ) | |
Sub colorSw(ByRef targetRange, ByRef colorCase As Long) | |
Select Case colorCase | |
Case 0 | |
With targetRange.Interior | |
.Pattern = xlSolid | |
.PatternColorIndex = xlAutomatic | |
.ThemeColor = xlThemeColorAccent5 'カラーパレットのメイン色 | |
.TintAndShade = 0.8 '色の濃淡 | |
.PatternTintAndShade = 0 | |
End With | |
Case 1 | |
With targetRange.Interior | |
.Pattern = xlSolid | |
.PatternColorIndex = xlAutomatic | |
.ThemeColor = xlThemeColorAccent5 | |
.TintAndShade = 0.6 | |
.PatternTintAndShade = 0 | |
End With | |
End Select | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment