Excel: bedingte Formatierung Machen statische

Gibt es eine Möglichkeit, zu konvertieren, bedingte Formatierung, um statische Formatierung in Excel?

Ich versuche zu exportieren, die einen Bereich einer Excel-Tabelle in eine neue Arbeitsmappe, mit identischem Aussehen, aber keine Formeln, Verknüpfungen, etc. Das problem hier ist, dass ich die bedingte Formatierung, die sich auf Berechnungen außerhalb der exportiert-Bereich.

Habe ich versucht, speichern Sie die Arbeitsmappe .html, seltsam genug, ist die Formatierung zeigt im IE, aber nicht beim erneuten öffnen in Excel.

InformationsquelleAutor Martin | 2011-01-14
  1. 8

    Folgende Idee wurde übernommen von hier aus, zwar angepasst und einige neue bedingte Formatierung Strukturen und Ihre Bedürfnisse.

    Es funktioniert wie folgt: Gegeben sei eine Arbeitsmappe mit einigen bedingte Formatierung (erstellen Sie eine Kopie von Ihnen), Sie setzen in ein Sub() den Bereich von Zellen aus, die Sie verändern wollen, aus der abhängig von der gerade formatieren, und führen Sie das makro. Danach einfach manuell löschen, die bedingte Formate, und presto!

    Sorry für die code-Länge ... das Leben ist manchmal wie diese 🙁 

    Option Explicit
Sub a()

Dim iconditionno As Integer
Dim rng, rgeCell As Range
Set rng = Range("A1:A10")

For Each rgeCell In rng

   If rgeCell.FormatConditions.Count <> 0 Then
       iconditionno = ConditionNo(rgeCell)
       If iconditionno <> 0 Then
           rgeCell.Interior.ColorIndex = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex
           rgeCell.Font.ColorIndex = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
       End If
   End If
Next rgeCell

End Sub
Private Function ConditionNo(ByVal rgeCell As Range) As Integer

Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition

    For iconditionscount = 1 To rgeCell.FormatConditions.Count
        Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
        Select Case objFormatCondition.Type
           Case xlCellValue
               Select Case objFormatCondition.Operator
                   Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                  If ConditionNo > 0 Then Exit Function
              End Select

          Case xlExpression
            If Application.Evaluate(objFormatCondition.Formula1) Then
               ConditionNo = iconditionscount
               Exit Function
            End If
       End Select

    Next iconditionscount
End Function

Private Function Compare(ByVal vValue1 As Variant, _
                         ByVal sOperator As String, _
                         ByVal vValue2 As Variant) As Boolean

   If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application.Evaluate(vValue1)
   If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application.Evaluate(vValue2)

   If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
   If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)

   Select Case sOperator
      Case "=": Compare = (vValue1 = vValue2)
      Case "<": Compare = (vValue1 < vValue2)
      Case "<=": Compare = (vValue1 <= vValue2)
      Case ">": Compare = (vValue1 > vValue2)
      Case ">=": Compare = (vValue1 >= vValue2)
      Case "<>": Compare = (vValue1 <> vValue2)
   End Select
End Function
    • Danke, ich werde versuchen das am Montag. Ziemlich verrückt, dass es keine .isActive die Eigenschaft auf FormatCondition (oder einen direkten Weg, um die berechnete format einer Zelle).
    • Sie sollten zuerst @Chris' Antwort ist nicht genug. Es ist einfacher (aber Sie sind gezwungen, Sie zu löschen, alle Ihre Formeln in der Auswahl)
    • Ich landete einfach ändern meine Bedingungen, um nur einen link zu den Zellen im inneren der Bereich, den ich exportieren möchten, so sollten Sie weiterhin funktionieren. Deine Antwort-ist die generische Lösung für dieses problem, also wählte ich es als die richtige Antwort.
    • Ich bin zu bemerken, dass keine der Lösungen hier scheinen zu funktionieren, wenn die bedingte Formatierung festgelegt wurde mit Hilfe einer Formel. -_- ugh
    InformationsquelleAutor Dr. belisarius
  2. 4

    Ich hasse es, wenn Leute sagen "hey, warum bist du nicht dabei, die ganze Sache mit diesem anderen Weg", aber ich bin einfach nur werfen es da draußen: wenn ich wollte, dies zu tun in der Vergangenheit, ich habe es getan, indem Sie zuerst kopieren Sie das gesamte Arbeitsblatt, in Frage und dann kopieren und einfügen der Formeln als Werte (ohne sich zu bewegen Ihre Position an alle). Dieses einfrieren wird die bedingte Formatierung natürlich aber auch bedeutet, dass die Arbeitsmappe neu berechnen lassen Sie nicht mit den Werten, die sind nicht mehr angemessen für die Formatierung, die auf Ihnen sitzen.

    Wenn das nicht funktioniert, Belisar' code sieht gut aus.

    • Gar nicht schlecht, wenn es nicht gegen die anderen OP ' s braucht. +1
    • Thx - kopieren von Werten ist Teil des plans. Ein problem hierbei ist, dass ich brauche nur ein Teil der Tabelle, und löschen Sie die anderen Zeilen/Spalten, aber der Zustand Verknüpfungen zu Zellen, die sich außerhalb des Teils. Eine weitere (kleinere) problem mit kopieren von Werten ist, dass es tötet alle sub-Zell-Formatierung in text-Zellen.
    • Ich habe gerade versucht diese in Excel 2010 und es scheint nicht zu funktionieren. Können Sie im detail die Schritte, die Sie ergreifen, um zu kopieren Sie einen Bereich von Zellen mit bedingter Formatierung auf ein anderes Arbeitsblatt. Wenn ich einfügen Werte, die Formatierung vollständig verloren, wenn ich einfügen Werte und Quellformatierung, die conditional formatting Regeln sind auch für die neue kopiert Blatt :(.
    • Sorry, eigentlich wollte ich nicht machen dies sehr deutlich. Ich habe bearbeitet die Antwort.
    InformationsquelleAutor Chris Rae
  3. 2

    Ich habe zusammen Belisar und Cameron nach Vorne ist zusätzlich. Wählen Sie den Bereich, den Sie möchten zu frieren (große Auswahl kann eine Weile dauern). Ich habe bemerkt, wenn es um excel-Fehler, die auf Zellen möglicherweise eine Ausnahme, aber sonst ist das arbeiten groß auf Excel 2010. Übrigens, danke Euch allen!

    Option Explicit

Sub FreezeConditionalFormattingOnSelection()
    Call FreezeConditionalFormatting(Selection)
    Selection.FormatConditions.Delete
End Sub

Public Function FreezeConditionalFormatting(Rng As Range)
Rem Originally posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static
Rem Modified 2012-04-20 by gcl to:
Rem   (a) be a function taking target range as an argument, and
Rem   (b) to cancel any multiple selection before processing in order to work around a bug
Rem         in Excel 2003 wherein querying the formula on any cell in a multiple/extended selection
Rem         returns the conditional formatting on the first cell in that selection!
Rem   (c) return number of cells that it modified.

Dim iconditionno As Integer
Dim rgeCell As Range
Dim nCFCells As Integer
Dim rgeOldSelection As Range

Set rgeOldSelection = Selection 'new

nCFCells = 0
For Each rgeCell In Rng
    rgeCell.Select  'new

   If rgeCell.FormatConditions.Count <> 0 Then
       iconditionno = ConditionNo(rgeCell)
       If iconditionno <> 0 Then
           rgeCell.Interior.ColorIndex = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex

           rgeCell.Font.ColorIndex = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
           nCFCells = nCFCells + 1
       End If
   End If
Next rgeCell

rgeOldSelection.Select 'new

FreezeConditionalFormatting = nCFCells
End Function

Private Function ConditionNo(ByVal rgeCell As Range) As Integer
Rem posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static

Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition
Dim f3 As String

    For iconditionscount = 1 To rgeCell.FormatConditions.Count
        Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
        Select Case objFormatCondition.Type
           Case xlCellValue
               Select Case objFormatCondition.Operator
                   Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                  If ConditionNo > 0 Then Exit Function
              End Select

          Case xlExpression

            f3 = objFormatCondition.Formula1
            f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1, RelativeTo:=objFormatCondition.AppliesTo.Cells(1, 1))
            f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlR1C1, ToAbsolute:=xlAbsolute, RelativeTo:=rgeCell)
            f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)

            If Application.Evaluate(f3) Then
               ConditionNo = iconditionscount
               Exit Function
            End If
       End Select

    Next iconditionscount
End Function

Private Function Compare(ByVal vValue1 As Variant, _
                         ByVal sOperator As String, _
                         ByVal vValue2 As Variant) As Boolean

   If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application.Evaluate(vValue1)
   If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application.Evaluate(vValue2)

   If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
   If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)

   Select Case sOperator
      Case "=": Compare = (vValue1 = vValue2)
      Case "<": Compare = (vValue1 < vValue2)
      Case "<=": Compare = (vValue1 <= vValue2)
      Case ">": Compare = (vValue1 > vValue2)
      Case ">=": Compare = (vValue1 >= vValue2)
      Case "<>": Compare = (vValue1 <> vValue2)
   End Select
End Function
    InformationsquelleAutor zweettooth
  4. 1

    Danke zu Belisar für die sehr hilfreiche Antwort! Jedoch läuft er in einen Fehler in Excel 2003, bei der Abfrage der bedingten Formatierung-Formel auf eine Zelle in einer mehrere/erweiterte Auswahl gibt die Formel für die erste Zelle in der Auswahl! Um dies zu umgehen, musste ich Abbrechen, Markierung am Anfang und wieder am Ende. Ich habe auch seiner Subroutinen in eine Funktion, der eine Auswahl und gibt die Anzahl der Zellen, die geändert, Hinzugefügt und ein wrapper-Unterroutine, die gilt es in die aktuelle Auswahl und löscht die bedingte Formatierung (da es nicht mehr benötigt wird), so müssen Sie nicht mehr zu ändern, um hard-code Ihrer Ziel-Bereich.

    Option Explicit

Sub FreezeConditionalFormattingOnSelection()
    Call FreezeConditionalFormatting(Selection)
    Selection.FormatConditions.Delete
End Sub

Public Function FreezeConditionalFormatting(rng As Range)
Rem Originally posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static
Rem Modified 2012-04-20 by gcl to:
Rem   (a) be a function taking target range as an argument, and
Rem   (b) to cancel any multiple selection before processing in order to work around a bug
Rem         in Excel 2003 wherein querying the formula on any cell in a multiple/extended selection
Rem         returns the conditional formatting on the first cell in that selection!
Rem   (c) return number of cells that it modified.

Dim iconditionno As Integer
Dim rgeCell As Range
Dim nCFCells As Integer
Dim rgeOldSelection As Range

Set rgeOldSelection = Selection 'new

nCFCells = 0
For Each rgeCell In rng
    rgeCell.Select  'new

   If rgeCell.FormatConditions.Count <> 0 Then
       iconditionno = ConditionNo(rgeCell)
       If iconditionno <> 0 Then
           rgeCell.Interior.ColorIndex = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex
           rgeCell.Font.ColorIndex = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
           nCFCells = nCFCells + 1
       End If
   End If
Next rgeCell

rgeOldSelection.Select 'new

FreezeConditionalFormatting = nCFCells
End Function

Private Function ConditionNo(ByVal rgeCell As Range) As Integer
Rem posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static

Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition

    For iconditionscount = 1 To rgeCell.FormatConditions.Count
        Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
        Select Case objFormatCondition.Type
           Case xlCellValue
               Select Case objFormatCondition.Operator
                   Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                  If ConditionNo > 0 Then Exit Function
              End Select

          Case xlExpression
            If Application.Evaluate(objFormatCondition.Formula1) Then
               ConditionNo = iconditionscount
               Exit Function
            End If
       End Select

    Next iconditionscount
End Function

Private Function Compare(ByVal vValue1 As Variant, _
                         ByVal sOperator As String, _
                         ByVal vValue2 As Variant) As Boolean

   If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application.Evaluate(vValue1)
   If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application.Evaluate(vValue2)

   If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
   If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)

   Select Case sOperator
      Case "=": Compare = (vValue1 = vValue2)
      Case "<": Compare = (vValue1 < vValue2)
      Case "<=": Compare = (vValue1 <= vValue2)
      Case ">": Compare = (vValue1 > vValue2)
      Case ">=": Compare = (vValue1 >= vValue2)
      Case "<>": Compare = (vValue1 <> vValue2)
   End Select
End Function
    InformationsquelleAutor gcl
  5. 1

    Ich nahm diese neben-über-auf excel.tips.com um diese Arbeit zu machen für Excel 2010 angepasst und es für gcl-version von Belisar' post. Ersetzen Sie diese Zeile unter die xlExpression Fall:

    If Application.Evaluate(objFormatCondition.Formula1) Then

    Mit dabei:

    f3 = objFormatCondition.Formula1
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1, RelativeTo:=objFormatCondition.AppliesTo.Cells(1, 1))
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlR1C1, ToAbsolute:=xlAbsolute, RelativeTo:=rgeCell)
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)
If Application.Evaluate(f3) Then

    Macht es die Formel propagieren nach unten und ganz richtig.

    • Ich bekomme immer ein "variable nicht definiert" - Fehler, wenn ich dies tun.
    • Müssen Sie Dim f3 As String an den Anfang des Codes definieren Sie die f3-variable. Beachten Sie auch, dass Sie zu "übersetzen", die Formel aus der bedingten Formatierung (CF) wenn Sie ein nicht-englischen Gebietsschema verwenden von Excel. (Siehe die Antwort für wie das zu übersetzen)
    InformationsquelleAutor Cameron Forward
  6. 0

    Dieser Ansatz scheint gut zu funktionieren. Ich habe nur umgesetzt wird es für die hintergrund-Farben.

    Sub FixColor()
    Dim r
    For Each r In Selection
        r.Interior.Color = r.DisplayFormat.Interior.Color
    Next r
    Selection.FormatConditions.Delete
End Sub
    InformationsquelleAutor pooroldpedro
Schreibe einen Kommentar