Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Highlight a row without losing background colors

  Asked By: Carolina    Date: Sep 20    Category: MS Office    Views: 803
  

I have found this VBA code in McGimpsey & Associates side. It work very well
but the only problem is that went we close the workbook and we open it later
the last selected row before closing during the last time remained in
highlight color.

My idea is to restore the color index of that row before closing the file.


How can I do this??


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS As Long = 256
Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long
Dim i As Long
If Not rOld Is Nothing Then 'Restore color indices
With rOld.Cells
If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
For i = 1 To cnNUMCOLS
.Item(i).Interior.ColorIndex = nColorIndices(i)
Next i
End With
End If
Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
With rOld
For i = 1 To cnNUMCOLS
nColorIndices(i) = .Item(i).Interior.ColorIndex
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
End With
End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Jay Richards     Answered On: Sep 20

This does it:
Delete the following lines from the current code:

Const cnNUMCOLS As Long = 256
Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long

Insert the following lines in the declaration area of a standard
module:
Public Const cnNUMCOLS As Long = 256
Public nColorIndices(1 To cnNUMCOLS) As Long
Public Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Public rOld As Range


Add the following code  to the ThisWorkbook code module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim i As Long
With rOld.Cells
For i = 1 To cnNUMCOLS
.Item(i).Interior.ColorIndex = nColorIndices(i)
Next i
End With
End Sub

That's it.

The only thing that's required is to select a different row  from the
active cell to restart the row highlighting but this could be
corrected by commenting out or deleting the line:

If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore

but would add to row flicker if moving left and right in the same row.

 
Answer #2    Answered By: Wade Jordan     Answered On: Sep 20

I suffer badly from spam and use Spamhaus spam blocker, on my email
system at home. Before I download email I take a look at my pop3
account to delete any obvious spam and phishing emails before before
the even get to my machine. I noticed there was an email from you and
expected to see it when I did a download, only it transpires that
Spamhaus blocked your email, and deleted it before I got it. If you
have any messages, please post them at the group otherwise I will
never see it.

 
Didn't find what you were looking for? Find more on Highlight a row without losing background colors Or get search suggestion and latest updates.




Tagged: