Interior.ColorIndex vs. Conditional Formatting
02.08.2023 12:51:54
Tor
Ich faerbe mit folgendem Code bei bestimmten Eingaben die Zelle ein und passe die Schriftfarbe an.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rngRange As Range, rngCell As Range
Set rngRange = Range("F10:AJ159")
For Each rngCell In Range(Target.Address)
If Not Intersect(rngCell, rngRange) Is Nothing Then
Select Case UCase(rngCell.Value)
Case "LL"
ActiveSheet.Unprotect
rngCell.Interior.ColorIndex = 4 ' green
rngCell.Font.ColorIndex = 1 ' black
ActiveSheet.Protect
Case "LL½"
ActiveSheet.Unprotect
rngCell.Interior.ColorIndex = 4 ' green
rngCell.Font.ColorIndex = 1 ' black
ActiveSheet.Protect
Case "LOP"
ActiveSheet.Unprotect
rngCell.Interior.ColorIndex = 16 ' grey
rngCell.Font.ColorIndex = 2 ' white
ActiveSheet.Protect
Case "HLOP"
ActiveSheet.Unprotect
rngCell.Interior.ColorIndex = 16 ' grey
rngCell.Font.ColorIndex = 2 ' white
ActiveSheet.Protect
Case "SL"
ActiveSheet.Unprotect
rngCell.Interior.ColorIndex = 3 ' red
rngCell.Font.ColorIndex = 1 ' black
ActiveSheet.Protect
Case "SL½"
ActiveSheet.Unprotect
rngCell.Interior.ColorIndex = 3 ' red
rngCell.Font.ColorIndex = 1 ' black
ActiveSheet.Protect
Case Else
ActiveSheet.Unprotect
rngCell.Interior.ColorIndex = 2 ' none
ActiveSheet.Protect
End Select
End If
Next rngCell
Set rngRange = Nothing
End Sub
Das funktioniert auch super. Allerdings nicht, wenn per conditional formatting Zellen schon vorher eingefaerbt waren. Wie kann ich das Einfaerben vom conditional formatting ueberschreiben?
Vielen Dank fuer Eure Hilfe