Bedingte Formatierung Probleme VBA
07.04.2015 13:24:26
Christopher
ich habe folgendes Problem:
Ich habe ein ziemlich allgemein-gehaltendes Makro geschrieben, dass später von mehreren verschiedenen Dateien benutzt werden muss.
Es klappt alles soweit, bis auf eins:
Die bedingte Formatierung zieht sich etwas länger, als die Tabelle lang ist.
Liegt wahrscheinlich daran, dass es ein fest definiertet Bereich ist, jedoch kann ich diesen nicht variabel umändern.
Ich habe schon vieles Probiert, aber ich komm einfach nicht auf eine Lösung.
Meine beiden Dateien werden mit hochgeladen, folgendes ist zur Nachvollziehung zu tun:
-> Auf dem Desktop muss ein Ordner "Muterquoten Auswertung" angelegt werden.
Dort muss die Datei "auswertung.txt." und die "Musterdatei" hinterlegt sein.
nach Starten des Makros der "Musterdatei" (STRG+M), wird die Datei "auswertung.txt" formatiert.
Nun ist zu sehen, dass die Bedingte Formatierung in Spalte L ein bisschen mehr Platz in Anspruch nimmt, als Daten hinterlegt sind.
ACHTUNG! Mir ist klar, dass der defnierte Bereich bis 71 geht und die Daten aus der Textdatei bis Zeile 21. Ich müsste einen Befehl haben, der mir die bedingte Formatierung bis zur letzten Zeile der beschriebenen Tabelle macht.
Die mitgelieferte Textdatei in nur eine von vielen. Daher, dass das allgemein gehalten werden soll, gibt es auch andere Textdateien, die länger sind und auch mit dem selben Makro benutzt werden sollen.
EDIT: war leider nicht in der Lage, die Excel Datei hochzuladen, da xlsb Format.
Vielleicht könnt ihr mit dem Quellcode auch noch was anfangen:
Sub formatierung()
' formatierung Makro
Benutzer = VBA.Environ("Username")
ChDir "C:\Users\" & Benutzer & "\Desktop\Musterquoten Auswertung"
Workbooks.OpenText Filename:= _
"C:\Users\" & Benutzer & "\Desktop\Musterquoten Auswertung\auswertung.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(7, 1), Array(15, 1), Array(43, 1), Array(47, 1), Array(69, 1), Array(80, 1), _
Array _
(89, 1), Array(101, 1), Array(111, 1), Array(125, 1), Array(135, 1)), _
TrailingMinusNumbers:=True
Range("B4").Select
Selection.Cut
Range("O8").Select
ActiveSheet.Paste
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Columns("A:C").Select
Selection.Delete Shift:=xlToLeft
Range("C1:I1").Select
Selection.ClearContents
Range("C1").Select
ActiveCell.FormulaR1C1 = ""
Range("C1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Anzahl A."
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Anzahl P."
Range("K1").Select
ActiveCell.FormulaR1C1 = "P."
Range("K1").Select
Selection.Font.Bold = True
Range("L1").Select
ActiveCell.FormulaR1C1 = "P."
Range("L1").Select
Selection.Font.Bold = True
Range("K2").Select
ActiveCell.FormulaR1C1 = "V. %"
Range("L2").Select
ActiveCell.FormulaR1C1 = "Z. %"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Zeitraum:"
Range("N2").Select
ActiveCell.FormulaR1C1 = "Datum:"
Range("M4").Select
Selection.Cut
Range("O1").Select
ActiveSheet.Paste
Range("O2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("O1").Select
Selection.NumberFormat = "m/d/yyyy"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]*100/RC[-3],"""")"
Range("K9").Select
ActiveWindow.SmallScroll Down:=-12
Range("L3").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]*100/RC[-4],"""")"
Range("L4").Select
ActiveWindow.SmallScroll Down:=39
Rows("58:63").Select
Selection.Delete Shift:=xlUp
Range("O55").Select
ActiveWindow.SmallScroll Down:=-69
Dim lRow As Long
On Error Resume Next 'löscht letzte Zeile
lRow = Range("A1:N71").Find("*", searchdirection:=xlPrevious).Row
Range("A" & lRow, "N" & lRow).ClearContents
On Error Resume Next 'Löscht wieder die letzte Zeile
lRow = Range("A1:N71").Find("*", searchdirection:=xlPrevious).Row
Range("A" & lRow, "N" & lRow).ClearContents
On Error Resume Next 'Löscht wieder die letzte Zeile
lRow = Range("A1:N71").Find("*", searchdirection:=xlPrevious).Row
Range("A" & lRow, "N" & lRow).ClearContents
On Error Resume Next 'löscht wieder die letzte Zeile
lRow = Range("A1:N71").Find("*", searchdirection:=xlPrevious).Row
Range("A" & lRow, "N" & lRow).ClearContents
Range("K3").Select
Selection.AutoFill Destination:=Range("K3:K71"), Type:=xlFillDefault
Range("K3:K71").Select
Range("N60").Select
ActiveWindow.SmallScroll Down:=-42
Range("L3").Select
Selection.AutoFill Destination:=Range("L3:L71"), Type:=xlFillDefault
Range("L3:L71").Select
Range("N61").Select
ActiveWindow.SmallScroll Down:=-45
Range("K3:K71").Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""?_);_(@_)"
Range("L3:L71").Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""?_);_(@_)"
Range("K1:K71").Select
Selection.Font.Bold = True
Range("L1:L71").Select
Selection.Font.Bold = True
Range("L3").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L3>=70"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L3