Reference to same word in excel

[karthikin]

HI Guys, i have a word 'AUCHAN' in COl G cell 9 and the same word exists in other cols too. When i click on the cell with the name 'AUCHAN', is it possible to refer other cells with same name by linking(by displaying it in color)???

Thank you.

[patel]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sc = Target.Value
ncol = 6
ActiveSheet.UsedRange.Interior.ColorIndex = 0
For Each cell In ActiveSheet.UsedRange
sc = Selection.Value
If cell = sc Then cell.Interior.ColorIndex = ncol
Next
End Sub

[karthikin]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sc = Target.Value
ncol = 6
ActiveSheet.UsedRange.Interior.ColorIndex = 0
For Each cell In ActiveSheet.UsedRange
sc = Selection.Value
If cell = sc Then cell.Interior.ColorIndex = ncol
Next
End Sub


Hi Patel thank u very much for your reply. Could you please explain a bit more.
When i click on AUCHAN, I want to Indicate the other cells with the same word.http://s23.postimg.org/vrtsoi5l3/Ptael.jpg (http://postimg.org/image/vrtsoi5l3/)

[patel]

did you put the code in a module or in sheet area ?
attach a sample file

[karthikin]

Thanks for your reply once again. I have attached a sample file.

[patel]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sc = Target.Value
ncol = 6
Range("A5").CurrentRegion.Interior.ColorIndex = 0
For Each cell In Range("A5").CurrentRegion
If InStr(cell.Value, sc) > 0 Then cell.Interior.ColorIndex = ncol
Next
End Sub

[SamT]

Patel, That looks so handy, I am saving it to my Personal.xls Troubleshooting Module.

I use the Rename to make the sub easier to find; to prevent conflicts with other Workbook_SelectionChange procedures in the same Module; And because a Workbook Procedure just doesn't belong in that module.


Option Explicit
Option Private Module

Private Sub HighLightSimilarCells(ByVal WkSht As Object, _
ByVal Target As Excel.Range)
''''Rename Sub to:
'Workbook_SheetSelectionChange
''''Color all Cells with Same Word
'Paste Macro into ThisWorkbook Code Pane.
'Select only one cell to highlight.
'Select two or more cells to clear highlighting.
Dim WordToFind As String
Dim HighLightColor As Long
Dim Cel As Range
WordToFind = Target.Text
HighLightColor = 6
With WkSht
'''If more than one Cell selected, clear all highlights
If Target.Count > 1 Then
Target.CurrentRegion.Interior.ColorIndex = 0
Else
''''Highlight similar Cells
'Clear previous highlighting.
Target.CurrentRegion.Interior.ColorIndex = 0
For Each Cel In Target.CurrentRegion
If InStr(Cel.Text, WordToFind) > 0 Then _
Cel.Interior.ColorIndex = HighLightColor
Next 'Cel
End If
End With
End Sub

[SamT]

After some thought, I changed the two "*.Text"s to CStr(*.Value)'s to account for formatted numbers.

[karthikin]

Thank you guys. Awesome.