Active Cell Highlight

[Dazzybeeguy]

The following highlights the active cell, when another cell is selected it leaves the previous one still highlighted, is there an easy fix whereby the cell reverts to its previous setting when the next cell is selcted?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveCell.Interior.ColorIndex = 6
End Sub

 

[Georgiboy]

Perhaps the below will help:

Dim rng As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not rng Is Nothing Then
        rng.Interior.Color = xlNone
    End If
    Target.Interior.ColorIndex = 6
    Set rng = Target
End Sub

 

[Dazzybeeguy]

Perhaps the below will help:

Dim rng As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not rng Is Nothing Then
        rng.Interior.Color = xlNone
    End If
    Target.Interior.ColorIndex = 6
    Set rng = Target
End Sub

This works fine if no the cell being selected has no colour etc, if it has this removes that formatting. How can it be changed to revert the cell back to its original settings when you click on a subsequent cell.

Thanks

 

[Peter_SSs]

Try this

1. Select the whole worksheet (by clicking the box at the top left at the intersection of the column labels and row labels). We could select a lesser range but applying it to the whole worksheet doesn't seem to make files too big or slow in my experience.
   
   
2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =AND(CELL("row")=ROW(),CELL("col")=COLUMN()) -> Format... -> On the Fill tab select the colour you want** -> Ok -> Ok
   
   ** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.
   
   
3. Right click the sheet name tab and choose 'View Code'
   
   
4. Copy and Paste the code below into the main right hand pane that opens at step 3.
   
   
5. Close the Visual Basic window & test.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

An advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

 

[Georgiboy]

This works fine if no the cell being selected has no colour etc, if it has this removes that formatting. How can it be changed to revert the cell back to its original settings when you click on a subsequent cell.

Thanks

You could also reduce the range that the Worksheet_SelectionChange sub works on if it is changing headers etc. You could also make it remember the colour and put it back after the selection has changed. You also have another option above from Peter, it also depends on the structure of your data to understand the best solution.

 

[Peter_SSs]

You could also make it remember the colour and put it back after the selection has changed.

That could be a bit difficult if the remembered colour happened to be applied by conditional formatting or if the selection wasn't changed until the next time the workbook was opened.

 

[Georgiboy]

Yes @Peter_SSs hence the last part of my post.

depends on the structure of your data to understand the best solution

I think the below is also important no matter the solution:

reduce the range that the Worksheet_SelectionChange sub works on

 

[Peter_SSs]

I think the below is also important no matter the solution:

It could be. I only commented on two of the possible problems of 'remembering' the previous colour and re-applying it.