How to temporarily highlight a cell when using the Find search function.

[DFCarter]

I think what you are referring to is the colour of the selected cell indicator.

That is not the cell border you're looking at. From my research, not possible to alter the colour of this application feature. Best you can probably do is change the background colour of the selected cell. If that's of interest let me know. If so, I presume you'd want the cell colour to go back to what it was before?

Hi Micron,
I would be interested in this solution, with the cell return to normal.
I will post my own question for this. But thought I’d grab your attention f you don’t mind. Thank you
Dan

 

[DFCarter]

Hi All,

We have a large shared spreadsheet with 100s of rows on 10s of sheets.
There are a variety of users with varied abilities. Some have asked if there is a way when using the Find function to highlight to target cell (as they struggle to see the cell being framed) but then the cell needs to go back to normal when they finish or search for something else.

Thank You
Dan

 

[Micron]

Don't know if I'll see that so I'll post one method here. It assumes that if a cell has a colour other than the default that you would not want to colour it. You can use a different basic colour than vbred or get fancier and use RGB values for more choices. Note: you need a module level variable, which I've named Lastrng (last range).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Lastrng Is Nothing Then
    Set Lastrng = Selection
    If Lastrng.Interior.ColorIndex = -4142 Then Lastrng.Interior.Color = vbRed
    Exit Sub
End If

Lastrng.Interior.ColorIndex = -4142
Set Lastrng = Selection
If Selection.Interior.ColorIndex = -4142 Then Selection.Interior.Color = vbRed

End Sub

If you do create a thread for this, please link back to this post if you use this as a solution.

 

[DFCarter]

Don't know if I'll see that so I'll post one method here. It assumes that if a cell has a colour other than the default that you would not want to colour it. You can use a different basic colour than vbred or get fancier and use RGB values for more choices. Note: you need a module level variable, which I've named Lastrng (last range).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Lastrng Is Nothing Then
    Set Lastrng = Selection
    If Lastrng.Interior.ColorIndex = -4142 Then Lastrng.Interior.Color = vbRed
    Exit Sub
End If

Lastrng.Interior.ColorIndex = -4142
Set Lastrng = Selection
If Selection.Interior.ColorIndex = -4142 Then Selection.Interior.Color = vbRed

End Sub

If you do create a thread for this, please link back to this post if you use this as a solution.

Thank you for that. But I don’t understand any of that. Never done anything with the coding in excel or any form. I tried to type it out word for word but wouldn’t work. Any tips?

 

[Micron]

I noticed that you're using Mac so I can't say if anything I write will work for you.
To try, copy that code but do not include Sub or End Sub lines (1st and last). Select the sheet that this is for, then open vb editor (alt+F11). That sheet may be highlighted in the list of wb sheets at the top left. If not, double click it there. Top left drop down, select "worksheet". Right side drop down, select Selection Change from the list of events. That will create a new sub. Paste code. At the very top, insert this:
Option Explicit
Dim Lastrng As Range

Save wb. Try selecting different cells on that sheet. It probably won't be perfect as is. F'rinstance, the last selected cell might get coloured and if the wb is saved, next time its opened that cell will be coloured. You can fix that by selecting one that isn't, then that one that was when wb opened. That should revert it.

 

[abdelfattah]

Hi Micron
your code works when select cell then highlight red.
what I try when select red cell then should be not color ,but doesn't delete color from the cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lastrng  As Range
If Lastrng Is Nothing Then
    Set Lastrng = Selection
    If Lastrng.Interior.ColorIndex = -4142 Then Lastrng.Interior.Color = vbRed
    Exit Sub
End If

If Lastrng.Interior.Color = vbRed Then
 Selection.Interior.ColorIndex = -4142
 End If

End Sub

any idea to fix that.

​

 

[DFCarter]

I noticed that you're using Mac so I can't say if anything I write will work for you.
To try, copy that code but do not include Sub or End Sub lines (1st and last). Select the sheet that this is for, then open vb editor (alt+F11). That sheet may be highlighted in the list of wb sheets at the top left. If not, double click it there. Top left drop down, select "worksheet". Right side drop down, select Selection Change from the list of events. That will create a new sub. Paste code. At the very top, insert this:
Option Explicit
Dim Lastrng As Range

Save wb. Try selecting different cells on that sheet. It probably won't be perfect as is. F'rinstance, the last selected cell might get coloured and if the wb is saved, next time its opened that cell will be coloured. You can fix that by selecting one that isn't, then that one that was when wb opened. That should revert it.

Thank you for the run down. I’ll be trusting this at work tomorrow. It’s windows at work. Where I need it to be. And I have a Mac at home. I will give an update tomorrow once I get to test it out again. Thanks again

 

[Jeffrey Mahoney]

Here is a solution, you'll need to two things. Create a Condition Formatting with the formula below. I used yellow fill as my format for that CF. Then add the VBA code below.

=AND(CELL("Row")=ROW(),CELL("col")=COLUMN())

Add this code to the SHEET Module level. In VBA, double click on the sheet name and past this code.

Private Sub Worksheet_SelectionChange(ByVal target As Range)

      ActiveSheet.EnableFormatConditionsCalculation = False
      ActiveSheet.EnableFormatConditionsCalculation = True

End Sub

 

[Micron]

Hi Micron
your code works when select cell then highlight red.
what I try when select red cell then should be not color ,but doesn't delete color from the cell
any idea to fix that.

Already addressed that, I think:

You can fix that by selecting one that isn't, then that one that was when wb opened. That should revert it.

 

[Fluff]

@DFCarter
In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

This time I have merged both threads.