Highlight active cell without removing color fills from other cells?

[otisvillain]

Hey all,

This is a code I have for highlighting the active cell, so that it sticks out when keying through a worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 28


End Sub

The problem is this: It removes all fill from the worksheet, and I need to keep certain columns highlighted. I'd like to have the columns keep their fill, while only the active cell highlights. Cannot find a way around this. Any ideas?

Thanks for the help

 

[akmatz]

Did you try commenting out the first line, like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Cells.Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 28
End Sub

 

[Rick Rothstein]

The problem is this: It removes all fill from the worksheet, and I need to keep certain columns highlighted. I'd like to have the columns keep their fill, while only the active cell highlights. Cannot find a way around this. Any ideas?

How many rows of data do you have?

How many columns of data do you have?

 

[jtakw]

Hi,

I think this will help you set it up the way you want: https://www.youtube.com/watch?v=NFngtL2OoWo

 

[otisvillain]

How many rows of data do you have?

How many columns of data do you have?

11 columns, 520 rows

 

[Rick Rothstein]

11 columns, 520 rows

Good, that is not a lot of data (the following is somewhat slow if there is a lot of data). This SelectionChange event code will color the row(s) and column(s) radiating out from the selected cell(s) yellow (actually, one less than the actual color of yellow in order to be able to preserve actual yellow colored cells) without changing the color of cells containing an existing color. See if it does what you want...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = 65534 'one less than vbYellow's value
  Application.ReplaceFormat.Interior.Color = xlNone
  Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = xlNone
  Application.ReplaceFormat.Interior.Color = 65534 'one less than vbYellow's value
  Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Target.EntireColumn.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.ScreenUpdating = True
End Sub

 

[otisvillain]

Good, that is not a lot of data (the following is somewhat slow if there is a lot of data). This SelectionChange event code will color the row(s) and column(s) radiating out from the selected cell(s) yellow (actually, one less than the actual color of yellow in order to be able to preserve actual yellow colored cells) without changing the color of cells containing an existing color. See if it does what you want...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = 65534 'one less than vbYellow's value
  Application.ReplaceFormat.Interior.Color = xlNone
  Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = xlNone
  Application.ReplaceFormat.Interior.Color = 65534 'one less than vbYellow's value
  Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Target.EntireColumn.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.ScreenUpdating = True
End Sub

Excellent, thank you for the reply! I'll try this code out and see how it works. I appreciate your help

 

[otisvillain]

What happens when I do that is the active cell stays filled with the color, even when I move into another cell

 

[jtakw]

Have you checked the link I provided in post #4?