Excel: Faire la mise en forme conditionnelle statique

Est-il un moyen de convertir la mise en forme conditionnelle la mise en forme statique dans Excel?

J'essaye d'exporter une plage d'une Feuille Excel vers un nouveau Classeur, avec la même apparence, mais pas les formules, les liens, etc. Le problème ici c'est que j'ai mise en forme conditionnelle qui s'appuie sur des calculs exportés à l'extérieur de gamme.

J'ai essayé d'enregistrer le classeur .html, bizarrement, la mise en forme affiche dans IE mais pas lors de la réouverture dans Excel.

OriginalL'auteur Martin | 2011-01-14

L'idée suivante a été prise à partir d'ici, bien que modifiés pour s'adapter à certains nouvelle mise en forme conditionnelle des structures et de vos besoins.

Il fonctionne comme ceci: étant Donné un classeur avec une certaine mise en forme conditionnelle (faire une copie de la vôtre), vous les mettez dans des Sous a() la plage de cellules que vous voulez transformer, de conditionnelle à droite de formatage, et exécuter la macro. Après cela, il suffit de supprimer manuellement les formats conditionnels, et hop!

Désolé pour la longueur de code ... la vie est parfois comme cela 🙁

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

Merci, je vais essayer ça lundi. Assez fou qu'il n'y a pas .est actif bien sur FormatCondition (ou un moyen direct d'obtenir l'calculée format d'une cellule).
Vous devriez d'abord vérifier est @Chris réponse n'est pas assez. Il est plus facile (mais vous êtes obligé de supprimer l'ensemble des formules de la gamme)
J'ai fini par en changeant simplement mes instructions conditionnelles à seulement un lien vers les cellules à l'intérieur de la zone que je veux à l'exportation, ainsi, ils doivent continuer à travailler. Votre réponse est le générique de solution à ce problème, j'ai donc sélectionné comme la réponse correcte.
Je remarque qu'aucune des solutions ici semblent fonctionner si le format conditionnel a été défini à l'aide d'une formule. -_- pouah

OriginalL'auteur Dr. belisarius

4

Je déteste quand les gens disent "hey, pourquoi ne pas vous faire toute chose, cette autre façon", mais je vais jeter là-bas: quand j'ai voulu le faire dans le passé, je l'ai fait en copiant tout d'abord l'ensemble de la feuille de calcul en question, puis de le copier et de coller les formules de valeurs (sans déplacer de leur emplacement). Ce gel, la mise en forme conditionnelle évidemment, mais signifie également que de recalculer le classeur de ne pas vous laisser avec des valeurs qui ne sont plus appropriées pour la mise en forme qui est assis sur eux.

Si cela ne fonctionne pas, bélisaire code ressemble beaucoup.

Pas mal du tout si elle ne viole pas les autres OP besoins. +1
Thx - la copie des valeurs fait partie du plan. Un problème, c'est que j'ai besoin de seulement une partie de la feuille et supprimer les autres lignes/colonnes, mais à la condition des liens vers les cellules en dehors de la partie. Un autre (mineur) problème avec la copie des valeurs, c'est qu'il tue tous les sous-cellule de mise en forme dans les cellules de texte.
J'ai juste essayé ce dans Excel 2010, et il ne semble pas fonctionner. Pouvez-vous détailler les étapes à suivre pour copier une plage de cellules avec une mise en forme conditionnelle à une autre feuille de calcul. Quand je colle , la mise en forme est complètement perdu, quand j'ai coller les valeurs et la mise en forme source, le conditional formatting règles sont également copiés dans la nouvelle fiche :(.
Désolé, en fait - je n'ai pas très claire à ce sujet. J'ai édité la réponse.

OriginalL'auteur Chris Rae

J'ai mis en place Bélisaire et Cameron l'attaquant de plus. Vous devez sélectionner la zone que vous souhaitez figer (grandes sélections pourrait prendre un certain temps). J'ai remarqué que si il y a excel erreurs sur les cellules, il peut provoquer une exception, mais sinon c'est génial de travailler sur Excel 2010. En passant, merci à vous tous!

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

OriginalL'auteur zweettooth

Grâce à Bélisaire pour la réponse utile! Cependant, il s'agit d'un bug dans Excel 2003 où l'interrogation de la formule de mise en forme conditionnelle sur n'importe quelle cellule dans un de plusieurs/sélection étendue renvoie la formule de la première cellule de la sélection! Pour contourner cela, j'ai dû annuler une sélection, au début et à la restaurer à la fin. J'ai aussi changé de sous-routine dans une fonction qui prend une fourchette et renvoie le nombre de cellules modifiées, et ajouté un wrapper sous-routine qui s'applique à la sélection en cours et supprime la mise en forme conditionnelle (puisqu'il n'est plus nécessaire), de sorte que vous n'avez plus besoin de le modifier en dur-code de votre zone cible.

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

OriginalL'auteur gcl

1

J'ai pris ce plus de à excel.tips.com pour faire ce travail pour Excel 2010 et l'a adapté pour gcl version de Bélisaire post. Remplacer cette ligne sous la xlExpression Cas:
```
If Application.Evaluate(objFormatCondition.Formula1) Then
```
:
```
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
```
Il rend la formule propager vers le bas et à travers correctement.

Je reçois un "variable non définie" erreur quand je fais cela.
Vous devrez ajouter Dim f3 As String au début du code pour définir la f3 variable. Notez aussi que vous aurez à "traduire" la formule venant de la mise en forme conditionnelle (CF) si vous utilisez une langue non anglaise d'Excel. (Voir cette réponse pour savoir comment le traduire)

OriginalL'auteur Cameron Forward

Cette approche semble bien fonctionner. J'ai seulement mis en œuvre pour les couleurs d'arrière-plan.

Sub FixColor()
Dim r
For Each r In Selection
r.Interior.Color = r.DisplayFormat.Interior.Color
Next r
Selection.FormatConditions.Delete
End Sub

OriginalL'auteur pooroldpedro

Vous devez vous connecter pour publier un commentaire.