AW: VBA Code aus 2013 funzt nicht in 2007
06.10.2015 14:54:18
Nilix
Hallo,
danke Michael, Dein Code hatte mich auf die Idee gebracht die Range in eine Variable zu schreiben , als String zu deklarieren und dann zum markieren des zu formatierenden Bereichs aufzurufen.
Habe nun mal den vollständigen Code angehängt da das Problem leider immer noch besteht und ich keine Lösung mehr weiß. Bitte schaut euch das nochmal an, habe viel Zeit investiert und nun geht es nicht :(
Vielen DAnk.
Sub BedingteFormatierung()
' Sucht die letzte Zeile mit inhalt.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
' Sucht die letzte Spalte mit inhalt.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
'bereich festlegen
Dim farbe As String
farbe = "$AI$9:" & Cells(LastRow - 5, LastColumn - 61).Address
Dim f60 As String
f60 = "$AI$9:$AI$" & LastRow
Dim f70 As String
f70 = "$AJ$9:$AJ$" & LastRow
Dim f80 As String
f80 = "$AK$9:$AK$" & LastRow
Dim f90 As String
f90 = "$AL$9:$AL$" & LastRow
Dim lz As String
lz = "$AW$9:$AW$" & LastRow
Range(farbe).Select
' alle formate loeschen
Selection.FormatConditions.Delete
'Format Rot/Weiß
Range(farbe).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=$AL$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(255, 255, 255)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 0, 0)
End With
'Format Orange
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$AL$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(0, 0, 0)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 128, 0)
End With
'Format Gelb
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$AL$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(0, 0, 0)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 255, 0)
End With
'Format Grün
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$AL$3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(0, 0, 0)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(0, 238, 0)
End With
'Format Weiß
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$AL$4"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(0, 0, 0)
End With
'Format Grau in Grau
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$AL$6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(131, 139, 131)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(131, 139, 131)
End With
'Größer gleich 60
Range(f60).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J9>=60"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
'hier stopt Excel die ausführung(fehlercode habe ich im moment nicht)
.Color = RGB(131, 139, 131)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(131, 139, 131)
End With
'Größer gleich 70
Range(f70).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J9>=70"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(131, 139, 131)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(131, 139, 131)
End With
'Größer gleich 80
Range(f80).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J9>=80"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(131, 139, 131)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(131, 139, 131)
End With
'Größer gleich 90
Range(f90).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J9>=90"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(131, 139, 131)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(131, 139, 131)
End With
'Format Grau in Schwarz
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J9>95"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(0, 0, 0)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(131, 139, 131)
End With
'Formatierung der zeit
Range(lz).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=0", Formula2:="=90"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(0, 128, 0)
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=90", Formula2:="=110"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 255, 0)
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=110"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 0, 0)
End With
'Ungleich test Weiß
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$H9""test"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = RGB(255, 255, 255)
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 255, 255)
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub