AW: VBA - jede zweite Zeile farbig - im best. Bereich!
15.01.2013 14:08:02
fcs
Hallo Dominic,
hier das Makro entsprechend angepasst, so dass alle Zellen im Bereich mit Rahmen formatiert werden.
Gruß
Franz
Sub ZeilenExportFormatieren()
Dim Zeile As Long, ZeileEnde As Long
'Zeilenbereich ermitteln der formatiert werden soll
Select Case ActiveWorkbook.Worksheets("Überblick").Range("B6").Value
Case 1: ZeileEnde = 67
Case 2: ZeileEnde = 80
Case 3: ZeileEnde = 95
Case Else
MsgBox "Unzulässiger Wert im Blatt ""Übersicht"" Zelle B6"
Exit Sub
End Select
With ActiveWorkbook.Worksheets("Export")
'Farben und Rahmen im gesamten Bereich zurücksetzen
Application.ScreenUpdating = False
With .Range("B16:O95")
.Interior.ColorIndex = xlColorIndexNone
.Borders.LineStyle = xlLineStyleNone
End With
'Jede 2. Zeile farbig formatieren
For Zeile = 16 To ZeileEnde Step 2
With .Range(.Cells(Zeile, 2), .Cells(Zeile, 15))
' .Interior.Color = VBA.RGB(Red:=255, Green:=255, Blue:=112) 'hellgelb
.Interior.Color = VBA.RGB(Red:=153, Green:=255, Blue:=204) 'hellblau
End With
Next Zeile
'Rahmen im Zellbereich einfügen
With .Range(.Cells(16, 2), .Cells(ZeileEnde, 15))
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
Application.ScreenUpdating = True
End With
End Sub