Sub Transpose_every_x_amount_of_columns_to_rows()
Application.ScreenUpdating = False
ArrPart1 = "INDIRECT(ADDRESS((CEILING(ROW()-ROW($A$2)+1,7)/7)+ROW($A$2)-1,1,,,""Sheet1""))"
ArrPart2 = "INDIRECT(ADDRESS((CEILING(ROW()-ROW($A$2)+1,7)/7)+1,COLUMN()-COLUMN($A$2)+COLUMN($A$2)-1+((MOD((ROW()-ROW($A$2)+1)-1,7)+1)-1)*IF((MOD((ROW()-ROW($A$2)+1)-1,7)+1)<=4,4,3)+IF((MOD((ROW()-ROW($A$2)+1)-1,7)+1)<=4,0,4)+1,,,""Sheet1""))"
With Worksheets("Sheet2").Range("A2:E500")
.Clear
'FormulaArray not works
'.FormulaArray = "=IF(COLUMN()-COLUMN($A$2)+1<=1,IF(CELL(""contents"",ArrPart1)<>"""",ArrPart1,""""),IF(COLUMN()-COLUMN($A$2)+COLUMN($A$2)-1<=IF((MOD((ROW()-ROW($A$2)+1)-1,7)+1)<=4,4,3),IF(CELL(""contents"",ArrPart2)<>"""",ArrPart2,""""),""""))"
.Formula = "=IF(COLUMN()-COLUMN($A$2)+1<=1,IF(CELL(""contents"",ArrPart1)<>"""",ArrPart1,""""),IF(COLUMN()-COLUMN($A$2)+COLUMN($A$2)-1<=IF((MOD((ROW()-ROW($A$2)+1)-1,7)+1)<=4,4,3),IF(CELL(""contents"",ArrPart2)<>"""",ArrPart2,""""),""""))"
.Replace "ArrPart1", ArrPart1
.Replace "ArrPart2", ArrPart2
Call Conditional_Formating(.Cells(1, 1).Resize(500, 1), .Cells(1, 1 + 1).Resize(500, 4))
End With
Application.ScreenUpdating = True
End Sub
Sub Conditional_Formating(DestRng1 As Range, DestRng2 As Range)
Dim FrstCll As String, ScndCll As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With DestRng1
FrstCll = .Cells(1, 1).Address(False, True)
ScndCll = .Cells(1, 1).Address(True, True)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & FrstCll & "<>"""",ROW()-ROW(" & ScndCll & ")+1>0)"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
.FormatConditions(1).StopIfTrue = False
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With DestRng2
FrstCll = .Cells(1, 1).Address(False, True)
ScndCll = .Cells(1, 1).Address(True, True)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & FrstCll & "<>"""",ROW()-ROW(" & ScndCll & ")+1>0,OR(MOD((ROW()-ROW(" & ScndCll & ")+1)-1,7)+1=1,MOD((ROW()-ROW(" & ScndCll & ")+1)-1,7)+1=3))"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = False
.Italic = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With .FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = -0.249946592608417
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & FrstCll & "<>"""",ROW()-ROW(" & ScndCll & ")+1>0,OR(MOD((ROW()-ROW(" & ScndCll & ")+1)-1,7)+1=2,MOD((ROW()-ROW(" & ScndCll & ")+1)-1,7)+1=4))"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & FrstCll & "<>"""",ROW()-ROW(" & ScndCll & ")+1>0,OR(MOD((ROW()-ROW(" & ScndCll & ")+1)-1,7)+1=5,MOD((ROW()-ROW(" & ScndCll & ")+1)-1,7)+1=7))"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(" & FrstCll & "<>"""",ROW()-ROW(" & ScndCll & ")+1>0,MOD((ROW()-ROW(" & ScndCll & ")+1)-1,7)+1=6)"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With
.FormatConditions(1).StopIfTrue = False
End With
End Sub