none
highlight row of active cell without losing existing color formatting RRS feed

  • Question

  • Hi there,

    I would like to highlight an entire row of an active cell BUT when you click another cell, I want the previous active row to return to its original color.

    e.g.

    As you can see below, the range is already colored in light grey with white borders. I managed to highlight the active row in yellow using VBA.

    But when i click another cell, it clears the color of the previous row (and leaves it white/no color). I just can't figure out how to make the rows to retain whatever color they were prior to being highlighted in yellow. In this example, i want the row to go back grey like other rows. 

    Is there a way to not lose the original color of a row, while highlighting the active row?

    p.s.

    I am aware that you can achieve this by converting the range to a table, but I need to make this workbook a shared workbook. When i try to share a table, Excel prompts you to convert a table to a range. If i could keep a table and still be able to share the workbook, that would be fantastic! But i guess that's another post... 

    Help please~~~

    Thanks



    • Edited by jay.nz Monday, April 3, 2017 3:01 AM
    Monday, April 3, 2017 2:55 AM

Answers

  • Hi jay.nz,

    Thank you for providing code.
    I've made a sample, and here's my code.
    In "ThisWorkbook":
    ' --- Workbook Open: get previous value when file closed before
    Private Sub Workbook_Open()
        With Worksheets("jay.nz")
            If (.Cells(65536, 1).Value = "") Then
                .prevRow = 1
                .prevCol = 1
            Else
                .prevRow = .Cells(65536, 1).Value
                .prevCol = .Cells(65536, 2).Value
            End If
            ' ---
            'MsgBox "prevRow = " & prevRow & Chr(13) & "prevCol = " & prevCol
            .prevInteriorColor = .Cells(.prevRow, 1).Interior.Color
            .prevBorderColor = .Cells(.prevRow, 1).Borders.Color
            ' ---
            .isIgnoreEvent = True
            .Cells(.prevRow, .prevCol).Select
            .isIgnoreEvent = False
        End With
    End Sub
    
    ' --- save current/present Row/Column for next open
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        With Worksheets("jay.nz")
            .Cells(65536, 1).Value = Selection.Row
            .Cells(65536, 1).Interior.Color = Selection.Interior.Color
            .Cells(65536, 2).Value = Selection.Column
            .Cells(65536, 2).Borders.Color = Selection.Borders.Color
        End With
    End Sub

    In Sheet where you want to change color:
    Public prevRow As Long
    Public prevCol As Long
    Public prevInteriorColor As Long
    Public prevBorderColor As Long
    
    Public isIgnoreEvent As Boolean
    
    ' --- Worksheet SelectionChange:
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        ActiveSheet.ScrollArea = "$A:$T"    ' -- fix ScrollArea(columns)
        ' ---
        If (Intersect(Target, Range("A1:T99999")) Is Nothing) Then Exit Sub
        ' ---
        If (Target.Row <> prevRow) Then
            Rows(prevRow).Interior.Color = prevInteriorColor
            Rows(prevRow).Borders.Color = prevBorderColor
            'MsgBox "Previous: " & prevRow & vbLf & "Current: " & Target.Row
            prevRow = Target.Row
            prevInteriorColor = Cells(Target.Row, 1).Interior.Color
            prevBorderColor = Cells(Target.Row, 1).Borders.Color
        End If
        ' ---
        If (isIgnoreEvent = True) Then
            Exit Sub
        End If
        ' --- set color: previous
        Rows(prevRow).Interior.Color = prevInteriorColor
        Rows(prevRow).Borders.Color = prevBorderColor
        ' --- set color: now/present
        Dim pRow As Integer
        pRow = Selection.Row
        With Rows(pRow).Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
    End Sub
    [note]
      Cells(65536, 1) and Cells(65536, 2) is for saving the latest Row, Column and Interior-color, Border-color when the file is closed. They will be user at the next opening.

    I hope this would be helpful.

    Regards,
    Ashidacchi

    Monday, April 3, 2017 8:22 AM
    1. What do I do if I want the row only up to a certain column (e.g. column A to Z) highlighted?
    2. I noticed that <Undo> in worksheet does not function while running this VBA. No big deal, but I just wanted to be sure that it is because of VBA running in the background.
    3. I don't know what i have done, but I keep getting this debug error. Any idea? I have even deleted the whole project and re-pasted the code in, but no joy. :-(

    1. change code like
    If (Intersect(Target, Range("A1:Z99999")) Is Nothing) Then Exit Sub
    
    2. "Undo" has no effect on VBA code.

    3. If you avoid Debug error, you needed to close the workbook and open it, because valuables are reset.
    So, I add code:
    If (prevInteriorColor = 0) Then
       Call ThisWorkbook.Workbook_Open
    End If
    [note] you should place "Workbook_Open" and "Workbook_BeforeClose(Cancel As Boolean)" in "ThisWorkbook", instead in "Module1".
    ________
    Hideki

    • Marked as answer by jay.nz Thursday, April 13, 2017 9:37 AM
    Thursday, April 13, 2017 1:35 AM

All replies

  • Hi jay.nz,

    Could you provide your code related to change color of active row?

    Regards,
    Ashidacchi
    Monday, April 3, 2017 4:43 AM
  • Hi Ashidacchi,

    This is the code i'm using. I googled it and changed a bit. I also added code to limit the scrollable area. 

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Static xRow
    Static xColumn
    ActiveSheet.ScrollArea = "$A:$T"
    If xColumn <> "" Then
            With Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If
    pRow = Selection.Row
    xRow = pRow
    With Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    End Sub 

    Hmmm... Something i just realised is that, with this VBA code, i CAN'T "undo". Which is not good at all!
    • Edited by jay.nz Monday, April 3, 2017 6:52 AM
    Monday, April 3, 2017 5:17 AM
  • Hi jay.nz,

    Thank you for providing code.
    I've made a sample, and here's my code.
    In "ThisWorkbook":
    ' --- Workbook Open: get previous value when file closed before
    Private Sub Workbook_Open()
        With Worksheets("jay.nz")
            If (.Cells(65536, 1).Value = "") Then
                .prevRow = 1
                .prevCol = 1
            Else
                .prevRow = .Cells(65536, 1).Value
                .prevCol = .Cells(65536, 2).Value
            End If
            ' ---
            'MsgBox "prevRow = " & prevRow & Chr(13) & "prevCol = " & prevCol
            .prevInteriorColor = .Cells(.prevRow, 1).Interior.Color
            .prevBorderColor = .Cells(.prevRow, 1).Borders.Color
            ' ---
            .isIgnoreEvent = True
            .Cells(.prevRow, .prevCol).Select
            .isIgnoreEvent = False
        End With
    End Sub
    
    ' --- save current/present Row/Column for next open
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        With Worksheets("jay.nz")
            .Cells(65536, 1).Value = Selection.Row
            .Cells(65536, 1).Interior.Color = Selection.Interior.Color
            .Cells(65536, 2).Value = Selection.Column
            .Cells(65536, 2).Borders.Color = Selection.Borders.Color
        End With
    End Sub

    In Sheet where you want to change color:
    Public prevRow As Long
    Public prevCol As Long
    Public prevInteriorColor As Long
    Public prevBorderColor As Long
    
    Public isIgnoreEvent As Boolean
    
    ' --- Worksheet SelectionChange:
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        ActiveSheet.ScrollArea = "$A:$T"    ' -- fix ScrollArea(columns)
        ' ---
        If (Intersect(Target, Range("A1:T99999")) Is Nothing) Then Exit Sub
        ' ---
        If (Target.Row <> prevRow) Then
            Rows(prevRow).Interior.Color = prevInteriorColor
            Rows(prevRow).Borders.Color = prevBorderColor
            'MsgBox "Previous: " & prevRow & vbLf & "Current: " & Target.Row
            prevRow = Target.Row
            prevInteriorColor = Cells(Target.Row, 1).Interior.Color
            prevBorderColor = Cells(Target.Row, 1).Borders.Color
        End If
        ' ---
        If (isIgnoreEvent = True) Then
            Exit Sub
        End If
        ' --- set color: previous
        Rows(prevRow).Interior.Color = prevInteriorColor
        Rows(prevRow).Borders.Color = prevBorderColor
        ' --- set color: now/present
        Dim pRow As Integer
        pRow = Selection.Row
        With Rows(pRow).Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
    End Sub
    [note]
      Cells(65536, 1) and Cells(65536, 2) is for saving the latest Row, Column and Interior-color, Border-color when the file is closed. They will be user at the next opening.

    I hope this would be helpful.

    Regards,
    Ashidacchi

    Monday, April 3, 2017 8:22 AM
  • Hi Ashidacchi,

    This is awesome! Thank you so much! :-D

    Just a couple more questions. 

    1. If i dont want any changes to the borders, do i just delete these lines? 

    2. Why do i need the code for the workbook "ThisWorkbook"? Can i not just use the code for the worksheet?

    Look forward to your reply! 

    Thank you again

    Tuesday, April 4, 2017 12:47 AM
  • Hi jay.nz,

    1. If i dont want any changes to the borders, do i just delete these lines? 

    Yes, you can delete them.
    And I recommend you to try make these lines remarks, i.e, add apostrophe before them.
    'PrevBorderColor = Cells(Target.Row, 1).Borders.Color

    2. Why do i need the code for the workbook "ThisWorkbook"? Can i not just use the code for the worksheet?


    In your previous code, you used Variables "Static".
    My code in "ThisWorkbook" is alternative to your "Static". So you can delete code (or make them remarks) in "ThisWorkbook", and revive "Static" as you did before.

    Let me make a supplementary comment:
    I intended to store/save information(Row no, Column no, and Color) of the last selected cell when the file is closed, and restore/get it when the file is opened.
    I used Cell [A65536] and [B65536] for this purpose.

    Now, I get noticed my mistake:
    Variable "isIgnoreEvent" is not necessary. And if lines related to "isIgnoreEvent" are deleted (or make them remarks), color Yellow will be seen when the file is opened next time. 
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        ....
        ' If (isIgnoreEvent = True) Then
        '    Exit Sub
        ' End If
        ....
    End Sub
    I hope this would answer your questions. 

    Regards,
    Ashidacchi

    P.S. 
    I've shared my sample file via Dropbox. Please download and check it.
    Excel Set-Get_Color.xlsm

    • Edited by Ashidacchi Tuesday, April 4, 2017 2:39 AM
    Tuesday, April 4, 2017 2:15 AM
  • Hi jay.nz,

    (1) If i dont want any changes to the borders, do i just delete these lines? 

    if you see the line of code that you are pointing then you can find that it is assigning the prevBorderColor on that line.

    if you remove that line then it will not assign that color to border.

    so it is possible that, when you change the color you will get some color difference.

    so before deleting or removing the line of code. just try to comment it and run the code again.

    if you find that it is working correctly then you can remove that line from the code.

    if you find the difference in output then you need to modify the code as per your requirement.

    (2) Why do i need the code for the workbook "ThisWorkbook"? Can i not just use the code for the worksheet?

    you can see that when you open the workbook , it will set some cells and assign interior color of that cells to variable.

    so because of this reason he uses Workbook_open event.

    there is no events like Worksheet_Open or Worksheet_Close.

    so you have to use Workbook events.

    I think that this suggestion will clear your questions. Ashidacchi will also give you suggestion when he will again visit this thread.

    I can see that your issue is solved now, so please try to mark the suggestion given by Ashidacchi as an answer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 4, 2017 2:24 AM
    Moderator
  • Thank you guys very much for your help!!!
    Wednesday, April 5, 2017 7:59 AM
  • Hi Ashidacchi,

    Sorry I have a few more questions if you don't mind...

    1. What do I do if I want the row only up to a certain column (e.g. column A to Z) highlighted?
    2. I noticed that <Undo> in worksheet does not function while running this VBA. No big deal, but I just wanted to be sure that it is because of VBA running in the background.
    3. I don't know what i have done, but I keep getting this debug error. Any idea? I have even deleted the whole project and re-pasted the code in, but no joy. :-(

    Thank you!

    Wednesday, April 12, 2017 9:33 AM
  • Hi jay.nz,

    No, I don't mind. You're always welcome.
    But it's dinner time in Japan. So, please wait till tomorrow.

    Well, before I will think of your questions, I'd like to request/ask you.

    (a) Could you send me your code?  I hope via email, or cloud storage such as Dropbox, OneDrive, for it's hard to read in this forum post. 

    (b) about your Q.3: 
    "Rows(prevRow).Interior.Color = prevInteriorColor"
    Which error No or Message?
    I suppose (Left member = Right member) can not be cast (not consistent), because property of Left member is "Color" and Right one is "Long" otherwise.

    # Sorry, my poor English. I can hardly use good English.
    I'm looking forward to your code.
    __________
    Hideki
    Wednesday, April 12, 2017 10:06 AM
  • Thank you so much Ashidacchi!

    A sample file is loaded here. 

    https://drive.google.com/open?id=0B1y9snTlTlZed1lleTNnVndmVHc 

    :-)

    Wednesday, April 12, 2017 10:53 AM
  • Thanks jay.nz,

    I could download and execute it.
    And found an error that you had shown in your post.

    It's past 20 o'clock in Japan (my country). Please wait till tomorrow.
    ___________
    HIdeki
    Wednesday, April 12, 2017 11:11 AM
  • No rush at all. I'm already grateful that you helped me this far! 
    Wednesday, April 12, 2017 11:17 AM
  • Hi jay.nz,

    Can I suppose your question/problem has been resolved?
    ______________
    Hideki
    Wednesday, April 12, 2017 10:40 PM
  • Hi jay.nz,

    I've shared a sample file via Dropbox.
    https://www.dropbox.com/s/2711gsgtzlr1s1e/Hideki_Color.xlsm?dl=0
    Download and check it.

    For my test, I removed "Conditional Formatting" you had made.
    ___________
    Hideki
    Thursday, April 13, 2017 1:05 AM
    1. What do I do if I want the row only up to a certain column (e.g. column A to Z) highlighted?
    2. I noticed that <Undo> in worksheet does not function while running this VBA. No big deal, but I just wanted to be sure that it is because of VBA running in the background.
    3. I don't know what i have done, but I keep getting this debug error. Any idea? I have even deleted the whole project and re-pasted the code in, but no joy. :-(

    1. change code like
    If (Intersect(Target, Range("A1:Z99999")) Is Nothing) Then Exit Sub
    
    2. "Undo" has no effect on VBA code.

    3. If you avoid Debug error, you needed to close the workbook and open it, because valuables are reset.
    So, I add code:
    If (prevInteriorColor = 0) Then
       Call ThisWorkbook.Workbook_Open
    End If
    [note] you should place "Workbook_Open" and "Workbook_BeforeClose(Cancel As Boolean)" in "ThisWorkbook", instead in "Module1".
    ________
    Hideki

    • Marked as answer by jay.nz Thursday, April 13, 2017 9:37 AM
    Thursday, April 13, 2017 1:35 AM
  • Hideki, you are AMAZING! Thank you so much for the script! Works like magic!

    :-D

    p.s. As for <Undo>, for example when i delete a value in a cell, click away from the cell, <Undo> to bring the value back in doesnt seem to work. I'll need to do some investigation. It could be my excel workbook.  

    Thursday, April 13, 2017 9:37 AM
  • Hi jay.nz,

    I hope you will make a new thread about <Undo>.
    I can hardly find the latest post in one thread, and want to be helpful to other new questions arising one after another.
    ______________
    HIdeki
    • Edited by Ashidacchi Thursday, April 13, 2017 11:01 AM
    Thursday, April 13, 2017 11:01 AM