VBA to highlight current selection with Find

[jake81]

I'm currently using conditional formatting and a simple script to highlight whatever cell I have selected. I am wondering if there is a better command to use than Worksheet_SelectionChange.

The only issue I have is when using "Find" to search the workbook, it will only select the first result found on each worksheet. Any subsequent results are not highlighted when you click "find next".

I'd like highlight to follow the results.

The formatting I am using is =ROW()=CELL("row")
My script is :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("HighlightRow")
.Name = "HighlightRow"
.RefersToR1C1 = "=" & ActiveCell.Row
If Application.CutCopyMode = False Then
Application.Calculate
End If
End With
End Sub

Thanks

 

[jolivanes]

What would you like to achieve when doing what?

 

[jake81]

I'd like each result in the find string to be highlighted, one at a time, as I push "find next".

Find already puts the green "select" ring around the cell, but it is apparently not actually changing the selection. So other than "Worksheet_SelectionChange" what command is it that puts the little green ring around the cell?? I assume I could use that and get exactly what I'm looking for.

 

[jolivanes]

Selection Change works on what it is advertised as: Selection Change, not on Find Next. You need to move to another cell to trigger it.
You can have a Button on the Sheet in question that when clicked on will take you, or highlight, to the next cell with the same value.
But you have to let us know for what range this should be, same column only, same row only, used range or whatever.

 

[jolivanes]

If you want to stay with the "SelectionChange", this should work on Column A.
Select a cell. The first cell that has the same value will get a green boder around it.
For your next find, select that green bordered cell (you work with "SelectionChange")
If you want all cells that have the same value as your selected cell to have a green border, you have to change your question to make that clear.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long, fnd As Variant
If Not Intersect(Target, Columns(1)) Is Nothing Then    '<---- Change Column (here column A = 1) as required
Columns(ActiveCell.Column).Borders.LineStyle = xlNone
With ActiveCell
    lastrow = Cells(Rows.Count, .Column).End(xlUp).Row
    fnd = Application.Match(.Value, Range(Cells(.Row + 1, .Column), Cells(lastrow, .Column)), 0)
    If Not IsError(fnd) Then .Offset(fnd, 0).BorderAround ColorIndex:=4, Weight:=xlThick
End With
End If
End Sub