0

I have two columns, the first column has a list of city names which are included next cell's text.

I want to check the second column for the city names. To make it easier I want to highlight city names in the second column's text.

I don't think it is possible to highlight a single word in text in Excel. I want to change the colour of the text, for easy recognition. I tried some highlight text rules but did not achieve what I want.

Is it possible, and if so, how can I do it?

enter image description here

Tony
  • 9,672
  • 3
  • 47
  • 75
t.ztrk
  • 99
  • 1
  • 9
  • Record a macro of you changing the colour of a few characters on one cell & you'll see how to do it in VBA. – Carol Dec 04 '17 at 22:53

1 Answers1

1

You can certainly search for the string in column A inside of Column B.

Are you any good with VBA code? This is more of a VBA project.

Here is the core code for selection and color change, but you could loop through your target cells and look at the cell range where they could be located each time.

Sub test4String2color()
Dim strTest As String
Dim strLen As Integer
 strTest = Range("F1")
 strLen = Len(strTest)
For Each cell In Range("A1:D100")
 If InStr(cell, strTest) > 0 Then
  cell.Characters(InStr(cell, strTest), strLen).Font.Color = vbRed
 End If
Next
End Sub

This was taken from: https://stackoverflow.com/a/11676031/8716187

Do you know how to make a range to loop through for the text to find targets? Really that is what would need to be added to this function which you can link to a button or something easy to call.

Tony
  • 9,672
  • 3
  • 47
  • 75
Wookies-Will-Code
  • 735
  • 9
  • 21
  • this worked but it just founds one word for each text, and not loop through all my search list. how can i solve these problems. – t.ztrk Dec 05 '17 at 13:22
  • you will have to write a loop on your cities list, so for each city it will look at each target text column. I can help you but you need to say if the cities will always be in column A (column 1) and the strings you want to search are always in Column B (column 2). if that is the case we can write something up that will perform actions for each cell in the range on the other cell range. - WWC – Wookies-Will-Code Dec 05 '17 at 20:55
  • yes that is exactly how my data is. i have list of cities in column A, and bunch of text in column B. i need to check them as fast as possible so cities need to be highlight by font color in text. thank you so much for enhancement of code. – t.ztrk Dec 05 '17 at 23:48
  • I can't upload the code here for that as the answer/topic is closed. – Wookies-Will-Code Dec 07 '17 at 16:38
  • If you follow the link to the solutions at the top I posted your code at the bottom. Cheers, WWC – Wookies-Will-Code Dec 07 '17 at 16:45