نصيحة: اللغات الأخرى مترجمة من قبل Google. يمكنك زيارة English نسخة من هذا الرابط.
تسجيل الدخول
x
or
x
x
التسجيل
x

or

كيفية تسليط الضوء على نص معين داخل خلية استنادا إلى نص آخر؟

في إكسيل، قد يكون من السهل بالنسبة لنا تسليط الضوء على الخلايا استنادا إلى نص معين، ولكن، هنا، أريد تسليط الضوء على نص معين داخل خلية لجعلها معلقة ولكن ليس الخلية بأكملها. هذا ربما مزعجة بالنسبة لمعظمنا. هذه المقالة، سوف أتحدث عن بعض الحيل لحل هذه المهمة في إكسيل.

حدد نصا معينا ضمن خلايا متعددة باستخدام شفرة فبا

حدد نصا محددا داخل خلية استنادا إلى نص آخر باستخدام شفرة فبا


السهم الأزرق الفقاعة اليمنى حدد نصا معينا ضمن خلايا متعددة باستخدام شفرة فبا


على سبيل المثال، لدي مجموعة من السلاسل النصية، والآن، أريد تسليط الضوء على النص المحدد "سماء"في هذه الخلايا للحصول على النتيجة كما يلي قطة عرض:

دوك تسليط الضوء على نص معين شنومك

لتسليط الضوء فقط على جزء من النص داخل الخلية، يمكن أن تساعدك التعليمات البرمجية فبا التالية.

1. حدد الخلايا التي تريد تمييز النص المحدد لها، ثم اضغط باستمرار ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

2. انقر إدراج > وحدة، ولصق التعليمة البرمجية التالية في وحدة نافذة او شباك.

رمز فبا: تسليط الضوء على جزء من النص داخل الخلية:

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. ثم اضغط F5 مفتاح لتشغيل هذا الرمز، وسوف مربع موجه يخرج لتذكيرك إدخال النص الذي تريد تسليط الضوء فقط، انظر الصورة:

دوك تسليط الضوء على نص معين شنومك

4. ثم انقر فوق OK زر، تم تمييز كل النص الذي حددته فقط داخل الخلايا، انظر الصورة:

دوك تسليط الضوء على نص معين شنومك


السهم الأزرق الفقاعة اليمنى حدد نصا محددا داخل خلية استنادا إلى نص آخر باستخدام شفرة فبا

وهنا حالة أخرى، لدي عمودين يحتوي العمود الأول على السلاسل النصية والعنوان الثاني هو النص المحدد، والآن، أحتاج إلى تسليط الضوء على النص النسبي في العمود الأول استنادا إلى النص المحدد في العمود الثاني.

دوك تسليط الضوء على نص معين شنومك

1. اضغط باستمرار ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

2. انقر إدراج > وحدة، ولصق التعليمة البرمجية التالية في وحدة نافذة او شباك.

رمز فبا: تسليط الضوء على جزء من النص داخل خلية استنادا إلى نص آخر:

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. بعد لصق الرمز، ثم اضغط F5 مفتاح لتشغيله، مربع موجه سوف يخرج لتذكيرك تحديد مجموعة البيانات التي تحتوي على حد سواء سلسلة النص والنص المحدد الذي تريد تسليط الضوء على واستنادا إلى، انظر قطة:

دوك تسليط الضوء على نص معين شنومك

4. ثم انقر فوق OK زر، وقد تم ملونة كل النص المقابل في العمود الأول على أساس نص معين في العمود الثاني الأحمر كما يلي قطة:

دوك تسليط الضوء على نص معين شنومك


Kutools for Excel يحل معظم مشاكلك ، ويزيد من إنتاجيتك بنسبة 80٪

  • إعادة استخدام: إدراج بسرعة الصيغ المعقدة ، الرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة المرور إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • سوبر الفورمولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (بسهولة قراءة وتحرير أعداد كبيرة من الخلايا) ؛ لصق على المدى المصفى...
  • دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ انقسام خلايا المحتوى ؛ الجمع بين تكرار الصفوف / الأعمدة... منع الخلايا المكررة. مقارنة النطاقات...
  • حدد تكرار أو فريد الصفوف. حدد صفوف فارغة (جميع الخلايا فارغة) ؛ سوبر البحث والعثور غامض في العديد من المصنفات ؛ اختيار عشوائي ...
  • نسخة طبق الأصل خلايا متعددة دون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة. إدراج الرصاصات، مربعات الاختيار والمزيد ...
  • استخراج النص، إضافة نص ، حذف حسب الموضع ، إزالة الفضاء. إنشاء وطباعة مجاميع ترحيل الصفحات ؛ تحويل بين محتوى الخلايا والتعليقات...
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ تصنيف متقدم حسب الشهر / الأسبوع / اليوم ، التردد وأكثر ؛ فلتر خاص بواسطة جريئة ، مائل ...
  • الجمع بين المصنفات وأوراق العمل. دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; دفعة تحويل XLS ، XLSX وقوات الدفاع الشعبي...
  • أكثر من ميزات 300 القوية. يدعم Office / Excel 2007-2019 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. ميزات كاملة النسخة التجريبية المجانية من 30.
علامة التبويب kte 201905

علامة تبويب Office توفر واجهة مبوبة لـ Office ، واجعل عملك أسهل بكثير

  • تمكين تحرير علامات التبويب والقراءة في Word و Excel و PowerPointوالناشر والوصول ، Visio ومشروع.
  • افتح مستندات متعددة وقم بإنشائها في علامات تبويب جديدة لنفس النافذة ، وليس في نوافذ جديدة.
  • يزيد إنتاجيتك بنسبة 50٪ ، ويقلل مئات نقرات الماوس لك كل يوم!
أسفل مكتبه
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 · 2 months ago
    Awesome. thanks
  • To post as a guest, your comment is unpublished.
    Iris Pereyra · 5 months ago
    This was very useful, thanks very much!
  • To post as a guest, your comment is unpublished.
    Darshan Smg · 5 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 · 5 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 · 8 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 · 5 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 · 9 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 · 9 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 · 10 months ago
    Is there a way to remove all those highlighted words?
    • To post as a guest, your comment is unpublished.
      skyyang · 9 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 · 11 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 · 11 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 · 11 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 · 1 years 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.