Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe specifieke tekst in een cel te markeren op basis van andere tekst?

In Excel kan het voor ons gemakkelijk zijn om de cellen te markeren op basis van een specifieke tekst, maar hier wil ik een specifieke tekst in een cel markeren om het uitstekend te maken, maar niet de hele cel. Dit is misschien een lastig voor de meesten van ons. In dit artikel zal ik ingaan op enkele trucs voor het oplossen van deze taak in Excel.

Markeer een specifieke tekst in meerdere cellen met VBA-code

Markeer specifieke tekst in een cel op basis van andere tekst met VBA-code


pijl blauwe rechterbel Markeer een specifieke tekst in meerdere cellen met VBA-code


Ik heb bijvoorbeeld een reeks tekststrings en ik wil nu de specifieke tekst markeren "Hemel"In deze cellen om het resultaat te krijgen als volgt: screenshot getoond:

doc markeer specifieke tekst 1

Om slechts een deel van de tekst in een cel te markeren, kan de volgende VBA-code u helpen.

1. Selecteer de cellen waarvan u de specifieke tekst wilt markeren en houd vervolgens de knop ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken bijvoegsel > moduleen plak de volgende code in de module Venster.

VBA-code: markeer een deel van de tekst binnen een cel:

Sub HighlightStrings()
'Updateby Extendoffice 20160704
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
cFnd = InputBox("Enter the text string to highlight")
y = Len(cFnd)
For Each Rng In Selection
  With Rng
    m = UBound(Split(Rng.Value, cFnd))
    If m > 0 Then
      xTmp = ""
      For x = 0 To m - 1
        xTmp = xTmp & Split(Rng.Value, cFnd)(x)
        .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
        xTmp = xTmp & cFnd
      Next
    End If
  End With
Next Rng
Application.ScreenUpdating = True
End Sub

3. Druk vervolgens op F5 toets om deze code uit te voeren, en een promptvenster verschijnt om u eraan te herinneren de tekst in te voeren die u alleen wilt markeren, zie screenshot:

doc markeer specifieke tekst 2

4. En klik vervolgens op OK knop, is alle tekst die u hebt opgegeven alleen in de cellen gemarkeerd, zie screenshot:

doc markeer specifieke tekst 3


pijl blauwe rechterbel Markeer specifieke tekst in een cel op basis van andere tekst met VBA-code

Hier is een andere situatie, ik heb twee kolommen waarvan de eerste kolom de tekenreeksen bevat en de tweede kolom de specifieke tekst, nu moet ik de relatieve tekst in de eerste kolom markeren op basis van de specifieke tekst in de tweede kolom.

doc markeer specifieke tekst 4

1. Houd de toets ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken bijvoegsel > moduleen plak de volgende code in de module Venster.

VBA-code: markeer een deel van de tekst in een cel op basis van een andere tekst:

Sub highlight()
'Updateby Extendoffice 20160704
    Dim xStr As String
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xChar As String
    Dim I As Long
    Dim J As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
LInput:
    Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Areas.Count > 1 Then
        MsgBox "not support multiple columns"
        GoTo LInput
    End If
    If xRg.Columns.Count <> 2 Then
        MsgBox "the selected range can only contain two columns "
        GoTo LInput
    End If
    For I = 0 To xRg.Rows.Count - 1
        xStr = xRg.Range("B1").Offset(I, 0).Value
        With xRg.Range("A1").Offset(I, 0)
            .Font.ColorIndex = 1
            For J = 1 To Len(.Text)
                If Mid(.Text, J, Len(xStr)) = xStr Then .Characters(J, Len(xStr)).Font.ColorIndex = 3
            Next
        End With
    Next I
End Sub

3. Na het plakken van de code en druk op F5 toets om het uit te voeren, verschijnt een prompt om u eraan te herinneren het datumbereik te selecteren dat zowel de tekststring bevat als specifieke tekst die u wilt markeren en die gebaseerd is op, zie screenshot:

doc markeer specifieke tekst 5

4. En klik vervolgens op OK knop, alle bijbehorende tekst in de eerste kolom op basis van de specifieke tekst in de tweede kolom is rood gekleurd als volgende schermafbeelding:

doc markeer specifieke tekst 6


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen zonder gegevens te verliezen; Inhoud gesplitste cellen; Combineer dubbele rijen / kolommen... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    leroy holmes · 1 months ago
    Awesome. thanks
  • To post as a guest, your comment is unpublished.
    Iris Pereyra · 4 months ago
    This was very useful, thanks very much!
  • To post as a guest, your comment is unpublished.
    Darshan Smg · 4 months ago
    Hi,
    Please any one help me. I want to highlight the specific number in same sentence. For ex : " 2 days leave scansion" want to highlight only "2" in sentence.
  • To post as a guest, your comment is unpublished.
    Darshan · 4 months ago
    Hi, anyone help me this. i want to highlight the Specific number in Cell within the same sentence. for Ex : " 2 days leave scansion " in this sentence want to highlight number.
  • To post as a guest, your comment is unpublished.
    vyshal Kumar · 7 months ago
    Hi,
    could anyone help me with the following

    my Cells in Column "G" contain the text from Column Z to AN, not compulsory that Column g contains all the text from Z to AN.

    My work here is to Highlight the text in Column G if it does not available in any of Column Z - AN

    For example : Cell G1 contains (Hello sir I am doing well) but The text "Sir" do not exist in Column "Z1" to "AN1"

    So i need to highlight the text "Sir"
  • To post as a guest, your comment is unpublished.
    Yaniv · 7 months ago
    i get a run-time error '13', type mismatch when i run the script. any suggestions?
    • To post as a guest, your comment is unpublished.
      Simon Proulx · 4 months ago
      I had the same issue; I found that one of my collumns were formulas and it was looking in them which was what triggered the error 13. Selected a range wihtout formula containing the text to highlight and it worked.
  • To post as a guest, your comment is unpublished.
    Madhusudhanan · 8 months ago
    HI
    I am regular visitor of your website. Your website is very useful for me. This code changes the font color. I want to highlight particular words (for example Insert, blank) in yellow background. I want to highlight this. Not just a font colour. Keep up the great work!!! Many thanks.
    • To post as a guest, your comment is unpublished.
      skyyang · 8 months ago
      Hi, Madhusudhanan,
      May be there is no direct way for highlighting part of text in a cell with background color in Excel worksheet.
      If you have other good method, please comment here.
  • To post as a guest, your comment is unpublished.
    Ace · 9 months ago
    Is there a way to remove all those highlighted words?
    • To post as a guest, your comment is unpublished.
      skyyang · 8 months ago
      Hi, Ace,
      To delete the text based on another column cell, the below vba code may help you, please try it.
      Sub DeleteStr()
      Dim xStr As String
      Dim xRg As Range
      Dim xTxt As String
      Dim xCell As Range
      Dim xChar As String
      Dim i As Long
      Dim J As Long
      On Error Resume Next
      If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
      Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
      End If
      LInput:
      Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
      If xRg Is Nothing Then Exit Sub
      If xRg.Areas.Count > 1 Then
      MsgBox "not support multiple columns"
      GoTo LInput
      End If
      If xRg.Columns.Count <> 2 Then
      MsgBox "the selected range can only contain two columns "
      GoTo LInput
      End If
      For i = 0 To xRg.Rows.Count - 1
      xStr = xRg.Range("B1").Offset(i, 0).Value
      With xRg.Range("A1").Offset(i, 0)
      .Font.ColorIndex = 1
      For J = 1 To Len(.Text)
      If Mid(.Text, J, Len(xStr)) = xStr Then
      .Characters(J, Len(xStr)).Delete
      End If
      Next
      End With
      Next i
      End Sub
  • To post as a guest, your comment is unpublished.
    Arturs · 1 years ago
    Is there a way to make the first VBA code in a way to work with a fixed list of keywords without a promt box? For example, I always want to find and mark keyword1, keyword2 and keyword3 in all text strings also when one string contains more than one of these keywords (all of them should get highlighted in the specific text string). And is it possible to make keyword1 and keyword2 red but the third one just bold?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Arturs,

      To highlight multiple keywords from a cell, the following vba code can help you:(you should separate the words by semicolon in the popped out input box)

      Sub HighlightStrings()
      Application.ScreenUpdating = False
      Dim Rng As Range
      Dim cFnd As String
      Dim xTmp As String
      Dim x As Long
      Dim m As Long
      Dim y As Long
      Dim xFNum As Integer
      Dim xArrFnd As Variant
      Dim xStr As String
      cFnd = InputBox("Please enter the text, separate them by semicolon:")
      If Len(cFnd) < 1 Then Exit Sub
      xArrFnd = Split(cFnd, ";")
      For Each Rng In Selection
      With Rng
      For xFNum = 0 To UBound(xArrFnd)
      xStr = xArrFnd(xFNum)
      y = Len(xStr)
      m = UBound(Split(Rng.Value, xStr))
      If m > 0 Then
      xTmp = ""
      For x = 0 To m - 1
      xTmp = xTmp & Split(Rng.Value, xStr)(x)
      .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
      xTmp = xTmp & xStr
      Next
      End If
      Next xFNum
      End With
      Next Rng
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        IvyCY · 10 months ago
        Hello Skyyang! I found there would be an error if running the code - it could not specify the word following a colon. Do you have any suggestion to improve it? Thank you very much!
        • To post as a guest, your comment is unpublished.
          skyyang · 10 months ago
          Hello, IvyCY,
          If you want to specify the words by a colon, you just need to change the semicolon symbol to colon, please apply the following code:

          Sub HighlightStrings()
          Application.ScreenUpdating = False
          Dim Rng As Range
          Dim cFnd As String
          Dim xTmp As String
          Dim x As Long
          Dim m As Long
          Dim y As Long
          Dim xFNum As Integer
          Dim xArrFnd As Variant
          Dim xStr As String
          cFnd = InputBox("Please enter the text, separate them by colon:")
          If Len(cFnd) < 1 Then Exit Sub
          xArrFnd = Split(cFnd, ":")
          For Each Rng In Selection
          With Rng
          For xFNum = 0 To UBound(xArrFnd)
          xStr = xArrFnd(xFNum)
          y = Len(xStr)
          m = UBound(Split(Rng.Value, xStr))
          If m > 0 Then
          xTmp = ""
          For x = 0 To m - 1
          xTmp = xTmp & Split(Rng.Value, xStr)(x)
          .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
          xTmp = xTmp & xStr
          Next
          End If
          Next xFNum
          End With
          Next Rng
          Application.ScreenUpdating = True
          End Sub
          • To post as a guest, your comment is unpublished.
            IvyCY · 10 months ago
            Thank you for your reply, skyyang!

            But it still didn't work.
            Perhaps my question was quite confusing. An example may make it clearer.
            For example, one of my keywords is "protect". The text in the cells might be displayed as "Our ways to solve the problem: protection and blablabla... " There might be spaces between ":" and "protection" because the formatting of the text is not always the same. Here, the keyword "protection" could not be highlighted by the codes above. That's the problem.

            I've tried several times but failed. I have also considered removing the punctuation in the cells, but it would compromise the comprehension of the texts. So I asked the question. Hope you don't mind.

            Thank you very much!

            Best regards!
  • To post as a guest, your comment is unpublished.
    lolster · 1 years ago
    hi, i noticed that this is case senitive highliter. Where can i change that it would highlite lower and upper casese in deired word
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, lolster,

      In the above two codes, which code do you want to identify case sensitive?
      We will modify the code for you!
      • To post as a guest, your comment is unpublished.
        elaine · 11 months ago
        Hi, I would like to ask the same question to highlight both lower and upper case, in the case of below:

        "find and mark keyword1, keyword2 and keyword3 in all text strings also when one string contains more than one of these keywords (all of them should get highlighted in the specific text string)"
  • To post as a guest, your comment is unpublished.
    Eugene Cloud · 1 years ago
    Thank you for "Highlight A Specific Text Within Multiple Cells With VBA Code" It works great. Would you please explain:
    line 18 xTmp = xTmp & Split(Rng.Value, cFnd)(x)
    Why idoes the "(x)" have to appear where it is? Is the value of x passed to the Split function as a named argument without the name? I would really like to understand this concept.Thank you for your help and this function. I have learned something I didn't know.
  • To post as a guest, your comment is unpublished.
    L · 1 years ago
    how could i get this code to work but have it highlight in a color other than red?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,
      If you want to highlight the text with other color, you just need to change the color index number 3 to other color index number in the below script:
      .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3

      Please try it. Thank you!
  • To post as a guest, your comment is unpublished.
    AMIT · 1 years ago
    Can anyone help me for a code to " copy those highlighted word in a different column ".
  • To post as a guest, your comment is unpublished.
    Alex · 1 years ago
    It worked great for me, thank you for sharing this with the world.
  • To post as a guest, your comment is unpublished.
    Angela · 1 years ago
    I get an error and when I debug it highlights this section m = UBound(Split(Rng.Value, cFnd))
  • To post as a guest, your comment is unpublished.
    Nic · 2 years ago
    Thank you!!!
  • To post as a guest, your comment is unpublished.
    W Law · 2 years ago
    I used Excel365. First VBA is not working. Second one cannot highlight correct keywords i.e. not working too
  • To post as a guest, your comment is unpublished.
    Umar · 2 years ago
    I have checked both given codes in excel 2016 but not working.
  • To post as a guest, your comment is unpublished.
    Pavlo · 2 years ago
    Thank you so much for your tip and macros. I can't imagine what I would do without them.