Script VBA para preencher células que contêm qualquer string de texto

Question

Estou tentando criar um script VBA que formate condicionalmente um intervalo de células que contenha qualquer sequência de texto com uma cor de preenchimento de minha escolha.

Até agora, usei uma regra de formatação condicional do Excel para conseguir isso e funciona; no entanto, arrastar e soltar o conteúdo das células de uma coluna para outra faz com que as regras de formatação condicional se tornem muito fragmentadas e rapidamente se tornem uma bagunça. O que começou como duas regras de formatação condicional, uma para a coluna A e outra para a coluna B, rapidamente se transforma em dezenas de regras separadas à medida que o Excel altera o campo "aplica-se a" das regras a cada cópia ou movimentação de dados de célula.

Um script VBA capaz de alcançar o mesmo que minhas regras de formatação condicional seria muito melhor, pois não seria afetado pela movimentação ou cópia e colagem de dados de células. Eu seria capaz de arrastar e soltar livremente meus dados na coluna apropriada sem que o código VBA subjacente fosse afetado.

Alguém aqui com alguma experiência básica em codificação VBA tem alguma idéia de um código simples que eu possa usar para simplesmente alterar a cor de preenchimento de qualquer célula que contenha alguma string? Isso se aplicaria às células A1:A200.

Se você não gostar de algo na minha pergunta por algum motivo, como David Postill fez recentemente, diga-me em um comentário e dê-me alguns minutos para atualizá-lo com qualquer informação adicional que você considere necessária, em vez de votar negativamente e fugindo.

Interessado apenas em ouvir pessoas com alguma experiência básica em VBA e desejo de ser útil. Nenhum comentário sarcástico sobre "Não vamos depurar algum script aleatório que você encontrou online para você", por favor. Eu só quero ouvir pessoas positivas e ÚTEIS.

Answer 1

É irritante que a formatação condicional possa ficar fragmentada, como você descreveu. Tento escrever regras de formatação condicional que se aplicam a uma coluna ou colunas inteiras. Então posso alterar um endereço fragmentado como $B$24,$B$25:$C$25,$B$27:$C$1048576,$B$26,$B$21:$C$23,$B$1:$C$19,$B$20voltar para $B:$C.

Já que você me lembrou desse incômodo, escrevi uma macro para corrigir endereços fragmentados em regras de formatação condicional. A macro só ajudará se as regras de formatação condicional se aplicarem a uma coluna ou colunas inteiras.

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

O pessoal lá deMrExcel.comconseguimos encontrar uma solução muito elegante.

Acontece que foi possível replicar a funcionalidade das minhas regras de formatação condicional existentes usando apenas cinco linhas de código VBA. A questão das regras serem alteradas à medida que os dados são movimentados não pode mais acontecer, pois a lógica de formatação condicional agora é tratada por uma pequena macro.

Passei alguns minutos testando isso e funciona bem. Agora excluí todas as minhas regras de formatação condicional e o mesmo comportamento de formatação condicional permanece através deste 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, aqui está todo o código VBA que uso agora nesta planilha.

A primeira seção trata da alfabetização automática, enquanto esta nova segunda seção trata da formatação 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