colore della formattazione condizionale convertirla in color

C

comtrex

New Member
Joined
Sep 12, 2006
Messages
15
Hi You of the forum, I should convert or to fill the cells that have a conditional formatting with some color, the same of the formatting and then to cancel anymore the content the color.

Or do you know how to cancel the content of a cell that and does format without touching the non colored cells?.

Regards

Comtrex

http://www.savefile.com/files/34135 esemple


Io dovrei riempire le celle che sono state colorate con la formattazione con un colore, poi tolgo la formattazione e li cancello mantenendo inalterato le celle senza formattazione.

Oppure cancellare le celel che hanno una formattazione senza alterare le altre.


Saluti
Comtrex



I should fill the cells that have been colored with the formatting with a color, then I remove the formatting and I cancel them maintaining unchanged the cells without formatting.

Or to cancel the cells that have a formatting without altering the others.


Regards
Comtrex
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
I do not speak Italian, but I do speak Spanish. Perhaps I can help?<ul>[*]Yes/No: You have cells with conditional formatting?[*]Yes/No: You want to take the cell's color that is the result of the conditional formatting and permanently format the cell with this color?[/list]No hablo italiano, pero hablo español. ¿Tal vez yo pueda ayudar?<ul>[*]Sí/No: ¿Usted tiene celdas con formatos condicionales?[*]Sí/No: ¿Usted quire pintar la celda en forma permanente el mismo color que en este momento es el resultado del formato condicional?[/list]
 
Upvote 0
Yo tengo que borrar los valores que son pintados con el formateo condicional.

Yo a Os pregunto tenéis una solución con el macro.

Saludos y gracias

Comtrex
 
Upvote 0
Si comprendo bien:
Rich (BB code): 
Sub ClearCellsWhereCFIsTrue()
    Dim rngTarget As Range, rngCell As Range
    
    '// change this address to correspond to your target
    '// or (better solution) use a named range.
    Set rngTarget = Range("A1:D10")
    
    For Each rngCell In rngTarget.Cells
        If udfCFColorIndex(rngCell) > 0 Then rngCell.ClearContents
    Next rngCell
End Sub

'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function udfCFColorIndex(rng As Range, _
                         Optional booOfText As Boolean = False) As Integer
'_____________________________________________________________________________

'//  adapted from http://www.cpearson.com/excel/CFColors.htm

    Application.Volatile
    Dim intAC As Integer
    Dim obj As Object
    
    intAC = ActiveCondition(rng)
    If intAC = 0 Then Set obj = rng Else Set obj = rng.FormatConditions(intAC)
    
    With obj
        udfCFColorIndex = IIf(booOfText, .Font.ColorIndex, .Interior.ColorIndex)
    End With

End Function
 
'//  The conditional format functions below are based on Chip Pearson's work at
'//  http://www.cpearson.com/excel/CFColors.htm
'//  Modified by Greg Truby, Jan. 2005

'_____________________________________________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function ActiveCondition(rng As Range) As Integer
'_____________________________________________________________________________
    
'//  adapted from http://www.cpearson.com/excel/CFColors.htm
        
    Dim intIndex As Integer
    Dim fc As FormatCondition
    Dim varTemp As Variant, varTemp2 As Variant
    
    ActiveCondition = 0
    
    If rng.FormatConditions.Count = 0 Then Exit Function
    
    For intIndex = 1 To rng.FormatConditions.Count
        Set fc = rng.FormatConditions(intIndex)
        Select Case fc.Type
                
            Case xlCellValue
                
                Select Case fc.Operator
                    Case xlBetween
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        varTemp2 = CStr(Application.Evaluate(fc.Formula2))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) >= CDbl(varTemp) _
                            And CDbl(rng.Value) <= CDbl(varTemp2) Then
                                ActiveCondition = intIndex
                            End If
                       Else
                            If TypeName(rng.Value) = "Date" Then
                                varTemp = CDate(varTemp)
                                varTemp2 = CDate(varTemp)
                            End If
                            If rng.Value >= varTemp _
                            And rng.Value <= varTemp2 Then
                                ActiveCondition = intIndex
                            End If
                       End If
    
                    Case xlGreater
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) > CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value > varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                           If CDbl(rng.Value) = CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If varTemp = rng.Value Then ActiveCondition = intIndex
                        End If
    
                    Case xlGreaterEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) >= CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value >= varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlLess
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) < CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value < varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlLessEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) <= CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value <= varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlNotEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) <> CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If varTemp <> rng.Value Then ActiveCondition = intIndex
                        End If
    
                   Case xlNotBetween
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        varTemp2 = CStr(Application.Evaluate(fc.Formula2))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                           If Not CDbl(rng.Value) <= CDbl(varTemp) _
                           And CDbl(rng.Value) >= CDbl(varTemp2) Then
                              ActiveCondition = intIndex
                           End If
                        Else
                            If TypeName(rng.Value) = "Date" Then
                                varTemp = CDate(varTemp)
                                varTemp2 = CDate(varTemp)
                            End If
                            If Not rng.Value <= varTemp _
                            And rng.Value >= varTemp2 Then
                                ActiveCondition = intIndex
                            End If
                        End If
                 
                   Case Else
                        Debug.Print "UNKNOWN OPERATOR"
               End Select

        Case xlExpression
            If Application.Evaluate(fc.Formula1) Then ActiveCondition = intIndex

        Case Else
            Debug.Print "UNKNOWN TYPE"
       End Select

    Next intIndex
    
End Function
 
Upvote 0
Good thing you provided the file. There is a quirk to the .formula1 property that VBA returns. Non-absolute references in formulae get returned incorrectly. It's really a bug in the program. The work-around is to SELECT the cell before retrieving any conditional format formulae. Also there was a flaw in the logic of one of the UDFs, too; so even w/o the MS bug, this had a problem. This now seems to work for me in testing against the workbook you provided.

Que bueno que usted proveó el archivo. Hay algo raro en la propiedad de .formula1 que devuelve VBA. En el caso de referencias no absolutos en fórmulas es erróneo. Realmente es una falla por parte de Microsoft. La solución es SELECCIONAR la celda antes de pedir la fórmula de formateo condicional. Además hubo un problema de lógica en una de las funciones asi que tuvimos problemas multiples. Ahora parece que me está funcionando bien en el cuaderno provisto por usted.

Rich (BB code): 
Sub ClearCellsWhereCFIsTrue()
    Dim rngTarget As Range, rngCell As Range
    
    '// change this address to correspond to your target
    '// or (better solution) use a named range.
    Set rngTarget = Range("N5:AL25")
    
    For Each rngCell In rngTarget.Cells
        'rngCell.Activate
        rngCell.Select
        'Debug.Print rngCell.Address, udfCFColorIndex(rngCell)
        If udfCFColorIndex(rngCell) > 0 Then rngCell.ClearContents
    Next rngCell
End Sub

'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function udfCFColorIndex(rng As Range, _
                         Optional booOfText As Boolean = False) As Integer
'_____________________________________________________________________________

'//  adapted from http://www.cpearson.com/excel/CFColors.htm

    Application.Volatile
    Dim intAC As Integer
    Dim obj As Object
    
    intAC = ActiveCondition(rng)
    If intAC = 0 Then Set obj = rng Else Set obj = rng.FormatConditions(intAC)
    
    With obj
        udfCFColorIndex = IIf(booOfText, .Font.ColorIndex, .Interior.ColorIndex)
    End With

End Function
  
'//  The conditional format functions below are based on Chip Pearson's work at
'//  http://www.cpearson.com/excel/CFColors.htm
'//  Modified by Greg Truby, Jan. 2005

'_____________________________________________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function ActiveCondition(rng As Range) As Integer
'_____________________________________________________________________________
    
'//  adapted from http://www.cpearson.com/excel/CFColors.htm
        
    Dim intIndex As Integer
    Dim fc As FormatCondition
    Dim varTemp As Variant, varTemp2 As Variant
    
    ActiveCondition = 0
    
    If rng.FormatConditions.Count = 0 Then Exit Function
    
    For intIndex = rng.FormatConditions.Count To 1 Step -1
        Set fc = rng.FormatConditions(intIndex)
        Select Case fc.Type
                
            Case xlCellValue
                
                Select Case fc.Operator
                    Case xlBetween
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        varTemp2 = CStr(Application.Evaluate(fc.Formula2))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) >= CDbl(varTemp) _
                            And CDbl(rng.Value) <= CDbl(varTemp2) Then
                                ActiveCondition = intIndex
                            End If
                       Else
                            If TypeName(rng.Value) = "Date" Then
                                varTemp = CDate(varTemp)
                                varTemp2 = CDate(varTemp)
                            End If
                            If rng.Value >= varTemp _
                            And rng.Value <= varTemp2 Then
                                ActiveCondition = intIndex
                            End If
                       End If
    
                    Case xlGreater
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) > CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value > varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                           If CDbl(rng.Value) = CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If varTemp = rng.Value Then ActiveCondition = intIndex
                        End If
    
                    Case xlGreaterEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) >= CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value >= varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlLess
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) < CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value < varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlLessEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) <= CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If rng.Value <= varTemp Then ActiveCondition = intIndex
                        End If
    
                    Case xlNotEqual
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                            If CDbl(rng.Value) <> CDbl(varTemp) Then ActiveCondition = intIndex
                        Else
                            If TypeName(rng.Value) = "Date" Then varTemp = CDate(varTemp)
                            If varTemp <> rng.Value Then ActiveCondition = intIndex
                        End If
    
                   Case xlNotBetween
                        varTemp = CStr(Application.Evaluate(fc.Formula1))
                        varTemp2 = CStr(Application.Evaluate(fc.Formula2))
                        If IsNumeric(varTemp) And TypeName(rng.Value) <> "String" Then
                           If Not CDbl(rng.Value) <= CDbl(varTemp) _
                           And CDbl(rng.Value) >= CDbl(varTemp2) Then
                              ActiveCondition = intIndex
                           End If
                        Else
                            If TypeName(rng.Value) = "Date" Then
                                varTemp = CDate(varTemp)
                                varTemp2 = CDate(varTemp)
                            End If
                            If Not rng.Value <= varTemp _
                            And rng.Value >= varTemp2 Then
                                ActiveCondition = intIndex
                            End If
                        End If
                  
                   Case Else
                        Debug.Print "UNKNOWN OPERATOR"
               End Select

        Case xlExpression
            'Debug.Print fc.Formula1, Evaluate(fc.Formula1)
            If Application.Evaluate(fc.Formula1) Then ActiveCondition = intIndex

        Case Else
            Debug.Print "UNKNOWN TYPE"
       End Select

    Next intIndex
    
End Function
 
Upvote 0
Podría apostarme la hoja que ha modificado para entender mejor la modificación.

Yo he copiado el macro que ha modificado pero de un error "run time 13."

Agradezco

Comtrex
 
Upvote 0
I have made different attempts with operational systems however I think that is guilt of excel that a leak has, I point out her the version that I use, in the attached photo it finds the detail.

The program and adjourned state completely the error him then I ask ago always her if you/he/she could have patience to correct a part to make to work her/it, I don't succeed in doing him/it.

He hecho muchas tentativas con sistemas operativos pero pienso que sea culpa de excel que tiene una brecha, le indico la versión que empleo, en la foto alegada encuentra el detalle.

EL programa y estado puestos al día completamente el error siempre lo hace entonces las pregunto si pudiera tener paciencia de corregir una parte para hacerla funcionar, yo no logro hacerlo.


Ho fatto diversi tentativi con sistemi operativi però penso che sia colpa di excel che ha una falla, le indico la versione che uso, nella foto allegata trova il dettaglio.

IL programma e stato aggiornato completamente l'errore lo fa sempre allora le chiedo se poteva avere pazienza di correggere una parte per farla funzionare, io non riesco a farlo.


La doy las gracias por su fundamental ayuda.





La parte que hace error y éste

If Application.Evaluate(fc.Formula1) Then ActiveCondition = intIndex


Si aplico una modificación

If Application.Evaluate(fc.Formula1) Then
ActiveCondition = intIndex

Debería funcionar, En cambio de error los últimos cordones
Case Else
Debug.Print "UNKNOWN TYPE"
End Select

Next intIndex

End Function

Esta parte no logro modificar
:-( :-?

Doy las gracias por ayuda y saludo

COMTREX
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
TAB in excel e clipboard windows
Replies
0
Views
172
Davide78
D
Gringoire
  • Question Question
Excel crashes without error messages after this subroutine is completed.
Replies
1
Views
624
Gringoire
Gringoire
M
  • Solved
Could you help me please with "run time error 1004 size property of the font class"
Replies
4
Views
550
DanteAmor
DanteAmor
C
  • Locked
  • Question Question
conversion color formatting for delete
Replies
2
Views
500
Von Pookie
Von Pookie

Forum statistics

Threads
1,223,958
Messages
6,175,629
Members
452,661
Latest member
Nonhle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top