ich habe ein macro aufgezeichnet die Zellen verbindet von L14:Z17, 2 Reihen 1x Gelb 1x Weiß,
dann mit dem Formatpinsel markiert und von L18:Z109 nach unten gezogen.
Habe dazu 2. Fragen
1. wie kann ich das macro verkürzen?
2. wie kann ich mit dem Formatpinsel die letzte Zeile mit Daten in L finden?
'Zellen verbinden
With Range("L14:L15")
.Merge
.HorizontalAlignment = xlCenter 'Zellenverbund bearbeiten
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36 'Gelb
.Borders.LineStyle = xlContinuous
End With
With Range("M14:M15")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("N14:N15")
.Merge
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("O14:O15")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("P14:P15")
.Merge
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("Q14:Q15")
.Merge
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("R14:R15")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("S14:S15")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("T14:Z15")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 36
.Borders.LineStyle = xlContinuous
End With
With Range("L16:L17")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2 'Weiß
.Borders.LineStyle = xlContinuous
End With
With Range("M16:M17")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
With Range("N16:N17")
.Merge
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
With Range("O16:O17")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
With Range("P16:P17")
.Merge
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
With Range("Q16:Q17")
.Merge
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
With Range("R16:R17")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
With Range("S16:S17")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
With Range("T16:Z17")
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
.Borders.LineStyle = xlContinuous
End With
'Mit Formatpinsel Tabelle formatieren (lz ist letzte Zeile)
Range("L14:Z17").Select
Selection.Copy
Range("L18:Z109").Select 'wie bekomm ich da nur bis zur letzten Zeile ausgefüllt?
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Vielen Dank im voraus
Christian