Início / computer / Perguntas / 1463536
Accepted
Mr Ethernet
Asked: 2019-07-25 05:47:49 +0800 CST

Script VBA para preencher células que contenham qualquer string de texto

  • 772

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

Até agora, uso 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 fiquem 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 torna dezenas de regras separadas à medida que o Excel altera o campo "aplica-se a" das regras a cada cópia ou movimentação dos dados da 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 por mover ou copiar e colar 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 ideia para um simples trecho de código que eu poderia usar para simplesmente alterar a cor de preenchimento de qualquer célula que contenha qualquer string? Isso se aplicaria às células A1:A200.

Se você não gostar de alguma coisa sobre minha pergunta por algum motivo, como David Postill fez recentemente, por favor, diga-me em um comentário e me dê alguns minutos para atualizá-lo com qualquer informação adicional que você acha que pode ser necessária, em vez de rejeitá-lo e fugindo.

Interessado apenas em ouvir pessoas com alguma experiência básica em VBA e um 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.

microsoft-excel vba

2 respostas

  1. É irritante que a formatação condicional possa se tornar fragmentada, como você descreveu. Eu 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$20de volta para $B:$C.

    Como você me lembrou desse aborrecimento, escrevi uma macro para corrigir endereços fragmentados em regras de formatação condicional. A macro ajudará apenas 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
    • 3
  2. Best Answer

    O pessoal do MrExcel.com conseguiu encontrar uma solução muito elegante.

    Acontece que foi possível replicar a funcionalidade de minhas regras de formatação condicional existentes usando apenas cinco linhas de código VBA. A questão das regras sendo alteradas conforme os dados são movidos 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 funcionou bem. Agora excluí todas as minhas regras de formatação condicional e o mesmo comportamento de formatação condicional continua por meio 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 agora uso nesta planilha.

    A primeira seção lida com a alfabetização automática enquanto esta nova segunda seção lida com a 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
    • 0

relate perguntas