CODEWITHSUNDEEP
excelvbatextcolorsmatching
Abdullah Al Mamun
Abdullah Al Mamun

Reputation: 41

How To Change text string color of matching string based on another cell text

Hope all are fine. I google about the above issue but did not find anything matching. So here I am asking the fact. Hope someone helps me to get through.

I am using an auto filter search. In Cell E5 what word do I type, the table of a specific range filtered based on that value of Cell E5.

My table ranges from B8:K1220. enter image description here

Now, I want the Text String of E5 and the matching Text string in the Table Range will be Highlighted as Yellow. If the E5 cells have nothing then all range color return to normal.

In Image where In E5 I type "Alex" then the matched Text String in the table Highlighted as Yellow. I want a code like this.

Hope someone help and really appreciate the creation of an amazing code.

Thanks in Advance

Upvotes: 1

Views: 1302

Answers (3)

VBasic2008
VBasic2008

Reputation: 54777

Conditional Formatting

  • Select the range B8:K1220 i.e. use Ctrl+G to open the "Go To" form and in the text box below "Reference:" enter B8:K1220 and press OK.

  • On the Ribbon, select Home > Conditional Formatting > New Rule to open the "New Formatting Rule" form.

  • In the list box below "Set a Rule Type:", select "Use a formula to determine which cells to format". In the text box below "Format values where this formula is true:" use the following formula:

    =SEARCH($E$5,B8)=1

  • After choosing the desired format (not described), confirm with OK twice.

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

enter image description here

Create a conditional formatting rule based on this formula:

=COUNTIF(A2;$E$5&"*")>0

Use conditional formatting to highlight information

Upvotes: 1

TehDrunkSailor
TehDrunkSailor

Reputation: 780

You can change the color of a range using the .Interior.Color of a range. The yellow color for highlighting is 65535.

You can change the color of a range's text using the .Font.Color of a range.

Private Sub Color()

    Dim MyCell As Range
    Set MyCell = ThisWorkbook.Sheets("Sheet1").Range("A3")
    
    MyCell.Interior.Color = 65535
    MyCell.Font.Color = 0

End Sub

Upvotes: 0

Related Questions