Code effizienter schreiben

Hallo Zusammen
Ich möchte meinen Code welchen ich initial mit Makro aufzeichnen erstellt habe besser schreiben. Leider fehlt mir hierzu das nötige Konow How. Einen Teil habe ich angepasst, jedoch glaube ich dass, die "with selection" Steps einiges an Verbesserungspotenzial haben, weil sich sehr viele wiederholen.
Ich hoffe jemand von euch kann mir unter die Arme greifen mit guten Inputs / Beispiel.
Liebe Gruess
Richi

'-------Alle bedingten Formatierungen löschen-------
wsZ.Cells.FormatConditions.Delete
'-------8 Wochen Ausblick-------
wsZ.Range(Cells(StartZ - 3, sJ), Cells(StartZ - 3, eJ)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=ARBEITSTAG(HEUTE();0)", Formula2:="=ARBEITSTAG(HEUTE();40)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 65535
End With
'-------Heute-------
wsZ.Range(Cells(StartZ - 3, sJ), Cells(StartZ - 3, eJ)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=ARBEITSTAG(HEUTE();0)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 6750054
End With
'-------Referenz Start Datum Samstage / Sonntage rot hinterlegen-------
lzZ = wsZ.Cells(wsZ.Rows.Count, "BB").End(xlUp).Row 'letzte Zeile Ziel ermitteln
wsZ.Range(Cells(StartZ + 2, 8), Cells(lzZ, 8)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($H15"""";ODER(WOCHENTAG($H15)=1;WOCHENTAG($H15)=7))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
End With
Selection.FormatConditions(1).StopIfTrue = False
'-------Abgabedatum Kunde Samstage / Sonntage rot hinterlegen-------
wsZ.Range(Cells(StartZ + 2, 9), Cells(lzZ, 9)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($I15"""";ODER(WOCHENTAG($I15)=1;WOCHENTAG($I15)=7))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
End With
Selection.FormatConditions(1).StopIfTrue = False
'-------Start Tsak Samstage / Sonntage rot hinterlegen-------
wsZ.Range(Cells(StartZ + 2, 27), Cells(lzZ, 27)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($AA15"""";ODER(WOCHENTAG($AA15)=1;WOCHENTAG($AA15)=7))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
End With
Selection.FormatConditions(1).StopIfTrue = False
'-------Start Datum Samstage / Sonntage rot hinterlegen-------
wsZ.Range(Cells(StartZ + 2, 28), Cells(lzZ, 28)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($AB15"""";ODER(WOCHENTAG($AB15)=1;WOCHENTAG($AB15)=7))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
End With
Selection.FormatConditions(1).StopIfTrue = False
'-------End Datum Samstage / Sonntage rot hinterlegen-------
wsZ.Range(Cells(StartZ + 2, 31), Cells(lzZ, 31)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($AE15"""";ODER(WOCHENTAG($AE15)=1;WOCHENTAG($AE15)=7))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
End With
Selection.FormatConditions(1).StopIfTrue = False
'-------End Task Samstage / Sonntage rot hinterlegen-------
wsZ.Range(Cells(StartZ + 2, 32), Cells(lzZ, 32)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($AF15"""";ODER(WOCHENTAG($AF15)=1;WOCHENTAG($AF15)=7))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
End With
Selection.FormatConditions(1).StopIfTrue = False
'-------Zeitstrahlfarben hinterlegen-------
lzZ = wsZ.Cells(wsZ.Rows.Count, "BB").End(xlUp).Row 'letzte Zeile Ziel ermitteln
lsZ = wsZ.Cells(6, wsZ.Columns.Count).End(xlToLeft).Column 'letzte Spalte Ziel ermitteln
wsZ.Range(Cells(StartZ + 3, StartSH), Cells(lzZ, lsZ)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""A-Z"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(5, 5).Value, wsS.Cells(5, 6).Value, wsS.Cells(5, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""A0100"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(6, 5).Value, wsS.Cells(6, 6).Value, wsS.Cells(6, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""B0210"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(7, 5).Value, wsS.Cells(7, 6).Value, wsS.Cells(7, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""B0220"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(8, 5).Value, wsS.Cells(8, 6).Value, wsS.Cells(8, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0010"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(9, 5).Value, wsS.Cells(9, 6).Value, wsS.Cells(9, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0020"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(10, 5).Value, wsS.Cells(10, 6).Value, wsS.Cells(10, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0030"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(11, 5).Value, wsS.Cells(11, 6).Value, wsS.Cells(11, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0040"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(12, 5).Value, wsS.Cells(12, 6).Value, wsS.Cells(12, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0050"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(13, 5).Value, wsS.Cells(13, 6).Value, wsS.Cells(13, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0011"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(14, 5).Value, wsS.Cells(14, 6).Value, wsS.Cells(14, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""B0230"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(15, 5).Value, wsS.Cells(15, 6).Value, wsS.Cells(15, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0100"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(16, 5).Value, wsS.Cells(16, 6).Value, wsS.Cells(16, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0110"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(17, 5).Value, wsS.Cells(17, 6).Value, wsS.Cells(17, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""B0100"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(18, 5).Value, wsS.Cells(18, 6).Value, wsS.Cells(18, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0120"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(19, 5).Value, wsS.Cells(19, 6).Value, wsS.Cells(19, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""B0101"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(20, 5).Value, wsS.Cells(20, 6).Value, wsS.Cells(20, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0130"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(21, 5).Value, wsS.Cells(21, 6).Value, wsS.Cells(21, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""C0012"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(22, 5).Value, wsS.Cells(22, 6).Value, wsS.Cells(22, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""D0100"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(23, 5).Value, wsS.Cells(23, 6).Value, wsS.Cells(23, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""E0100"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(24, 5).Value, wsS.Cells(24, 6).Value, wsS.Cells(24, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""E0200"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(25, 5).Value, wsS.Cells(25, 6).Value, wsS.Cells(25, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND($T15 =""E0300"";$AE15=CP$10)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(wsS.Cells(26, 5).Value, wsS.Cells(26, 6).Value, wsS.Cells(26, 7).Value)
End With
Selection.FormatConditions(1).StopIfTrue = False
'------------------------------------Endblock zur Geschwindigkeitserhöhung bei Schleifen------------------------------------
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

'-------Alle bedingten Formatierungen löschen------- wsZ.Cells.FormatConditions.Delete '-------8 Wochen Ausblick------- With wsZ.Range(Cells(StartZ - 3, sJ), Cells(StartZ - 3, eJ)) .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlBetween, _ Formula1:="=ARBEITSTAG(HEUTE();0)", _ Formula2:="=ARBEITSTAG(HEUTE();40)" .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).Interior.Color = 65535 .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlEqual, _ Formula1:="=ARBEITSTAG(HEUTE();0)" .FormatConditions(.FormatConditions.Count).SetFirstPriority .FormatConditions(1).Interior.Color = 6750054 End With '-------Referenz Start Datum Samstage / Sonntage rot hinterlegen------- lzZ = wsZ.Cells(wsZ.Rows.Count, "BB").End(xlUp).Row 'letzte Zeile Ziel ermitteln With wsZ.Range(Cells(StartZ + 2, 8), Cells(lzZ, 8)) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=UND($H15"""";ODER(WOCHENTAG($H15)=1;WOCHENTAG($H15)=7))" .FormatConditions(.FormatConditions.Count).SetFirstPriority With .FormatConditions(1) .Font.ThemeColor = xlThemeColorDark1 .Interior.PatternColorIndex = xlAutomatic .Interior.Color.Color = 255 .StopIfTrue = False End With End With

Beliebteste Forumthreads (12 Monate)

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread