Script VBA para llenar celdas que contienen cualquier cadena de texto

Question

Estoy intentando crear un script VBA que formatee condicionalmente un rango de celdas que contienen cualquier cadena de texto con un color de relleno de mi elección.

Hasta ahora, utilizo una regla de formato condicional de Excel para lograr esto y funciona; sin embargo, arrastrar y soltar el contenido de las celdas de una columna a otra hace que las reglas de formato condicional se fragmenten mucho y se conviertan rápidamente en un desastre. Lo que comenzó como dos reglas de formato condicional, una para la columna A y otra para la columna B, rápidamente se convierte en docenas de reglas separadas a medida que Excel altera el campo "se aplica a" de las reglas con cada copia o movimiento de datos de celda.

Un script VBA que pueda lograr lo mismo que mis reglas de formato condicional sería mucho mejor, ya que no se vería afectado al mover o copiar y pegar datos de celda. Podría arrastrar y soltar libremente mis datos en la columna apropiada sin que el código VBA subyacente se vea afectado.

¿Alguien aquí con alguna experiencia básica en codificación VBA tiene alguna idea para un fragmento de código simple que pueda usar para simplemente cambiar el color de relleno de cualquier celda que contenga una cadena? Se aplicaría a las celdas A1:A200.

Si por alguna razón no le gusta algo de mi pregunta, como le hizo David Postill recientemente, dímelo en un comentario y dame unos minutos para actualizarla con cualquier información adicional que creas que pueda ser necesaria, en lugar de rechazarla y escabulléndose.

Solo estoy interesado en escuchar a personas con alguna experiencia básica en VBA y con el deseo de ser útiles. No hagas comentarios sarcásticos sobre "No vamos a depurar ningún script aleatorio que hayas encontrado en línea", por favor. Sólo quiero escuchar a personas positivas y ÚTILES.

Answer 1

Es molesto que el formato condicional pueda fragmentarse, como usted describió. Intento escribir reglas de formato condicional que se apliquen a una columna o columnas completas. Entonces puedo cambiar una dirección fragmentada como $B$24,$B$25:$C$25,$B$27:$C$1048576,$B$26,$B$21:$C$23,$B$1:$C$19,$B$20volver a $B:$C.

Como me recordaste esta molestia, escribí una macro para corregir direcciones fragmentadas en reglas de formato condicional. La macro sólo será útil si las reglas de formato condicional se aplican a una columna o columnas completas.

Sub ApplyConditionalFormattingToEntireColumns()
    Dim oneFormatCondition As FormatCondition
    Dim strAddresses() As String, lngA As Long
    Dim strFirst As String, strLast As String, strCheck As String

    For Each oneFormatCondition In ActiveSheet.Cells.FormatConditions
        strFirst = ""
        strLast = ""
        'Splits each condition's addresses into an array.
        strAddresses = Split(oneFormatCondition.AppliesTo.Address, ",")
        For lngA = LBound(strAddresses) To UBound(strAddresses)
            'Finds and saves the first column.
            strCheck = strAddresses(lngA)
            strCheck = Mid(strCheck, 2, _
                InStr(2, strCheck, "$", vbTextCompare) - 2)
            If strFirst = "" Then strFirst = strCheck
            If strLast = "" Then strLast = strCheck
            If strFirst > strCheck Then strFirst = strCheck
            If strLast < strCheck Then strLast = strCheck
            'Finds and saves the last column.
            strCheck = strAddresses(lngA)
            If InStr(2, strCheck, ":", vbTextCompare) > 0 Then
                strCheck = Right(strCheck, Len(strCheck) - _
                    InStr(2, strCheck, ":", vbTextCompare))
                strCheck = Mid(strCheck, 2, _
                    InStr(2, strCheck, "$", vbTextCompare) - 2)
                If strLast < strCheck Then strLast = strCheck
            End If
        Next lngA
        'Modifies each condition's address to entire columns.
        oneFormatCondition.ModifyAppliesToRange _
            Range("$" & strFirst & ":$" & strLast)
    Next oneFormatCondition
End Sub

Answer 2

La gente deMrExcel.comPudimos encontrar una solución muy elegante.

Resulta que era posible replicar la funcionalidad de mis reglas de formato condicional existentes usando solo cinco líneas de código VBA. El problema de que las reglas se modifiquen a medida que se mueven los datos ya no puede ocurrir ya que la lógica de formato condicional ahora es manejada por una pequeña macro.

He pasado unos minutos probando esto y funciona bien. Ahora eliminé todas mis reglas de formato condicional y el mismo comportamiento de formato condicional continúa a través de este código VBA:

With Range("A1:B200")
  .Interior.Color = xlNone
  .Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 22
  .Offset(, 1).Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 36
End With

Para contextualizar, aquí está el código VBA completo que uso ahora en esta hoja de trabajo.

La primera sección maneja la alfabetización automática mientras que esta nueva segunda sección maneja el formato condicional:

Private Sub Worksheet_Change(ByVal Target As Range)

Range("A1:A200").Sort Key1:=Range("A1"), _
  Order1:=xlAscending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom

      Range("B1:B200").Sort Key1:=Range("B1"), _
  Order1:=xlAscending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom

With Range("A1:B200")
  .Interior.Color = xlNone
  .Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 22
  .Offset(, 1).Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 36
End With

End Sub