Vai al contenuto principale

Come colorare o evidenziare determinate parole nelle celle in Excel?

Se una cella contiene più parole, come puoi evidenziare una determinata parola solo in questa cella? E che ne dici di evidenziare questa determinata parola in più celle? Questo articolo fornirà un metodo per ottenerlo.

Colora determinate parole in una singola cella / più celle con codice VBA


Colora determinate parole in una singola cella / più celle con codice VBA

Il seguente codice VBA può aiutarti a evidenziare una determinata parola in una selezione. Si prega di fare quanto segue.

1. Seleziona l'intervallo che contiene le celle in cui desideri evidenziare una determinata parola. Quindi premere il pulsante altro + F11 tasti contemporaneamente per aprire il file Microsoft Visual Basic, Applications Edition finestra.

2. In apertura Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Moduli. Quindi copia e incolla il codice VBA nella finestra del codice.

Codice VBA: colora determinate parole nelle celle in Excel

Sub HighlightStrings()
    Dim xHStr As String, xStrTmp As String
    Dim xHStrLen As Long, xCount As Long, I As Long
    Dim xCell As Range
    Dim xArr
    On Error Resume Next
    xHStr = Application.InputBox("What is the string to highlight:", "KuTools For Excel", , , , , , 2)
    If TypeName(xHStr) <> "String" Then Exit Sub
    Application.ScreenUpdating = False
        xHStrLen = Len(xHStr)
        For Each xCell In Selection
            xArr = Split(xCell.Value, xHStr)
            xCount = UBound(xArr)
            If xCount > 0 Then
                xStrTmp = ""
                For I = 0 To xCount - 1
                    xStrTmp = xStrTmp & xArr(I)
                    xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3
                    xStrTmp = xStrTmp & xHStr
                Next
            End If
        Next
    Application.ScreenUpdating = True
End Sub

3. premi il F5 chiave per eseguire il codice. E nel Kutools for Excel finestra di dialogo, inserisci la parola specifica che evidenzierai nelle celle, quindi fai clic su OK pulsante. Vedi screenshot:

Quindi puoi vedere tutte le parole che hai specificato sono colorate in rosso nelle celle selezionate immediatamente come mostrato nell'immagine sottostante.


Articoli correlati:

I migliori strumenti per la produttività in ufficio

Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli    VLookup a valori multipli  |   VLookup su più fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalità in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule    Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle    Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   Più di 50 Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'età in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo.  Fai clic qui per ottenere la funzionalità di cui hai più bisogno...

scheda kte 201905


Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
Comments (24)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Is there any chance that I can do both coloring and make words Bond with that VBA code? Please help me :)
This comment was minimized by the moderator on the site
Bonjour Cristal,
Merci pour ce code. Est-il possible de l'adapter pour mettre en évidence plusieurs mots "Apple,Rose,Vert,Merci"
Merci
This comment was minimized by the moderator on the site
Hi Cous,

The following VBA code can help. After running the code, you will get a dialog box. Please type in the words you want to highlight and separate them by comma.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/highlight.png
Sub HighlightStrings()
'Updated by Extendoffice 20230130
    Dim xHStr As String, xStrTmp As String
    Dim xHStrLen As Long, xCount As Long, I As Long
    Dim xCell As Range
    Dim xArr
    Dim xArr2
    On Error Resume Next
    xHStr = Application.InputBox("What is the string to highlight:", "KuTools For Excel", , , , , , 2)
    If TypeName(xHStr) <> "String" Then Exit Sub
    Application.ScreenUpdating = False
    
    xArr2 = Split(xHStr, ",")
    For j = 0 To UBound(xArr2)
        xHStr = xArr2(j)
    
        xHStrLen = Len(xHStr)
        For Each xCell In Selection
            xArr = Split(xCell.Value, xHStr)
            xCount = UBound(xArr)
            If xCount > 0 Then
                xStrTmp = ""
                For I = 0 To xCount - 1
                    xStrTmp = xStrTmp & xArr(I)
                    xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3
                    xStrTmp = xStrTmp & xHStr
                Next
            End If
        Next
    Next
    
    Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Thank you. that was really helpful. Can someone please tell how to color the text instead of highlighting it?

Regards
This comment was minimized by the moderator on the site
Hi Shaik Faiaz hamad,

Excel does not allow coloring a part of a cell. I'm sorry I can't help you with this problem.
This comment was minimized by the moderator on the site
Thank you that is very useful. How can I Highlight a word instead of a font color?

Regards.
This comment was minimized by the moderator on the site
how could the script be altered to do the following?

increase the font by 1 size, and
highlight multiple words with one running of the script?

Thx!
This comment was minimized by the moderator on the site
Hi t.taln,

If you want to increae the font size by 1 and highlight multiple words at the same time, please add the following line after the line "xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3" in the VBA code.
Note: You need to know the current font size of the selected cell beforehand, and then enter a number one size larger than the original word. The number 12 in the line below is the font size I will assign to the matching words. And the original font size of the word is 11.
xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.Size = 12
This comment was minimized by the moderator on the site
That's very nice, thx! I'm wondering if anyone knows how to make it work on Mac? Many thanks
Rated 5 out of 5
This comment was minimized by the moderator on the site
Ciao,
a me servirebbe evidenziare tutti i numeri (comprensivi di due decimali) da -10,00 a 0 in rosso e da 0 a +10,00 in verde. come posso fare per non aggiungere singolarmente ogni dato senza aggiungerli tutti manualmente?

Grazie mille
This comment was minimized by the moderator on the site
Hi Ciao,
Are your numbers located in different cells in a range? If so, you can create two conditional formatting rules (between -10 and 0, between 0 and 10) to highlight these numbers. If not, can you upload a screenshot of your data?
This comment was minimized by the moderator on the site
Buna,

Coloreaza cuvantul doar daca e la inceput. Daca e la mijloc in aceeasi casuta de excel sau la sfarsit nu-l coloreaza.
Ce anume as putea schimba in cod pentru a-l colora indiferent unde se afla in casuta excel?

Multumesc!
This comment was minimized by the moderator on the site
Hi Andreea,
If you only want to highlight the word if it is at the beginning of the selected cells. The following VBA code can do you a favor. Please give it a try.
Sub HighlightStrings()
'Updated by Extendoffice 20220805
    Dim xHStr As String, xStrTmp As String
    Dim xHStrLen As Long, xCount As Long, I As Long
    Dim xCell As Range
    Dim xArr
    On Error Resume Next
    xHStr = Application.InputBox("What is the string to highlight:", "KuTools For Excel", , , , , , 2)
    If TypeName(xHStr) <> "String" Then Exit Sub
    Application.ScreenUpdating = False
        xHStrLen = Len(xHStr)
        For Each xCell In Selection
            If xHStrLen <= Len(xCell.Value) Then
                If xHStr = Left(xCell.Value, xHStrLen) Then
                    xCell.Characters(1, xHStrLen).Font.ColorIndex = 3
                End If
            End If
        Next
    Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Bonjour,
Est-il possible de supprimer la boite de dialogue et de mettre par défaut "apple" comme mot recherché ?
Merci
This comment was minimized by the moderator on the site
Hello PAUC,
The following code can do you a favor. Please give it a try.
Sub HighlightStrings()
'Updated by Extendoffice 20220721
    Dim xHStr As String, xStrTmp As String
    Dim xHStrLen As Long, xCount As Long, I As Long
    Dim xCell As Range
    Dim xArr
    On Error Resume Next
    xHStr = "apple"
    If TypeName(xHStr) <> "String" Then Exit Sub
    Application.ScreenUpdating = False
        xHStrLen = Len(xHStr)
        For Each xCell In Selection
            xArr = Split(xCell.Value, xHStr)
            xCount = UBound(xArr)
            If xCount > 0 Then
                xStrTmp = ""
                For I = 0 To xCount - 1
                    xStrTmp = xStrTmp & xArr(I)
                    xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3
                    xStrTmp = xStrTmp & xHStr
                Next
            End If
        Next
    Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Thanks... this was very helpful! Is there a way to adjust the macro so that it only highlights whole words instead of partials. For instance, I'm trying to highlight the word "design" but it highlights the "design" in the word "designate". I want it to skip over that word if it's not the whole word. Thanks!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations