I wish to find a word in excel and highlight it.How to do it?

Question

I wish to find a word in excel and highlight that cell.How to do it using VBA. My code is highlighting the entire sheet.

Here is the code-

Sub Foreign_Lang_Converter()

Sheets("Sheet2").Select
Value = 0
i = 1
Do While (Cells(i, 2) <> "")
Value = Value + 1
i = i + 1
Loop
Count = 0
For j = 1 To Value

a = Cells(j, 1)
b = Cells(j, 2)
Sheets("Sheet1").Select
Cells.Select
    Selection.Find What:=a
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
Selection.Replace What:=a, Replacement:=b
Sheets("Sheet2").Select
Next j
End Sub

Firstly, post the code that you have tried, otherwise we're going to find it tricky to spot the error in your code... — Dave, Jun 27 '16 at 08:23
Hi Dave , I have edited the question and added my code. Please have a look.!! — snigdha, Jun 27 '16 at 08:43

score 1 · Answer 1 · edited May 23 '17 at 11:58

First, read this.

edit: This is not a solution to your overall task, however I'll leave it up since it is the solution to the issue you described with your original code (it colored the entire sheet).

Your problem here is that Selection.Find What:=a doesn't change the selection, it returns a range (that goes nowhere). Since the whole sheet is still selected, the next steps color the whole sheet. Try

With Sheets("Sheet1").Cells.Find(a)
    With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    .Value = b
End With

This does only replace one occurrence though. Look into the .FindNext method or conditional formatting. Also it might be better to set the other search parameters (LookIn, LookAt, SearchOrder, and MatchByte) because they get saved. (see the remarks here)

edit: fixed code. Should work now.

Hi Dave, This code shows error. With .Interior and .Value = b have errors, Also desired output is not obtained i.e. it is not highlighting the replacements of sheet1 — snigdha, Jun 27 '16 at 11:14
@snigdha I'm not Dave but nevermind ;) . Please always specify what kind of error appears. When I run this code on a worksheet, it replaces *one* occurence of `a` with `b` and sets the color of that cell. — arcadeprecinct, Jun 27 '16 at 11:35

score 0 · Answer 2 · answered Jun 27 '16 at 11:35

I got the resolution,

The code would be like -

Sub Foreign_Lang_Converter()

Sheets("Sheet2").Select
Value = 0
i = 1
Do While (Cells(i, 2) <> "")
Value = Value + 1`enter code here`
i = i + 1
Loop
Count = 0
For j = 1 To Value

a = Cells(j, 1)
b = Cells(j, 2)
Sheets("Sheet1").Select


   Cells.Select
    Application.ReplaceFormat.Clear
    With Application.ReplaceFormat.Font
        .Subscript = False
        .TintAndShade = 0
    End With
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Replace What:=a, Replacement:=b, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True

Sheets("Sheet2").Select
Next j
End Sub

This is a better solution than the `Range.Find` method. However, you shouldn't use selections. See [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for more info on that. — arcadeprecinct, Jun 27 '16 at 11:38

I wish to find a word in excel and highlight it.How to do it?

2 Answers2