highlighting active row problem

[njbeancounter]

The following code removes all interior colors and then highlights the row of the active cell in yellow:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Cells.Interior.Color = xlNone
ActiveCell.Cells.EntireRow.Interior.Color = vbYellow
End Sub


How can I modify this to allow some rows to be highlighted a different color?

 

[JoeMo]

You want some fixed rows (which ones) to be highlighted color X (what is X) and the activecell's row to be yellow?

 

[njbeancounter]

I want to be able to keep some rows highlighted red, for instance. Not necessarily the same fixed row, just any row could be highlighted not yellow, and remain highlighted, but the yellow would still be highlighted as the active row.

 

[JoeMo]

I want to be able to keep some rows highlighted red, for instance. Not necessarily the same fixed row, just any row could be highlighted not yellow, and remain highlighted, but the yellow would still be highlighted as the active row.

Don't see how you would do that with a selection_change event unless the red rows are fixed. Otherwise, when you select a new cell, how would the rows you want to be red get communicated?

 

[njbeancounter]

I believe that right there is the logic I was wrestling with...

 

[Rick Rothstein]

If you do not mind highlighting only cells that have no color and leaving colored cells untouched even if in the highlighted row(s), then you can use this event code...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  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 = vbYellow
  Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
End Sub