+ Reply to Thread
Results 1 to 34 of 34

Need another solution for this function of highlighting of rows and cells

  1. #1
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Smile Need another solution for this function of highlighting of rows and cells

    Hi,


    Attched is my workbook.
    There is a VBA + a formula to highlight columns and rows.

    After a while this has started to make Excel and the workbook slow. As soon as I removed the formula from the formatting rules, the workbook is fast again.

    Is there another solution for this to work?

    Existing:

    VBA:


    Please Login or Register  to view this content.
    Formula:
    =ELLER(CELLE("col")=KOLONNE();CELLE("row")=RAD()) (In Norwegian)

    Regards
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Tirrazo; 04-18-2022 at 08:59 AM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    Clear all the conditional formats and use the Worksheet_SelectionChange event to identify the relevant row range and column range that needs a background colour and set the .Interior.ColorIndex property to say 15

    Start the macro by clearing the background colour from the whole of live range.

  3. #3
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    Thank you for the fast reply.
    I am not quite sure what you mean because someone else made this for me long ago.

    He made the formula to wotk with the VBA for visualizing the row and column.
    This seems to take a lot of process or memory.

    I got this VBA without conditional formatting, but this macro removes the color of everything.
    I don't want that.

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    This macro stores the last selected cell in A1 so that the cell referred to can be cleared next time a cell is selected, and will then paint the row and column of the cell selected in grey.
    Obviously the A1 cell could be anywhere.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    Thank you.

    Does it require conditional formatting?
    I can't make that VBA work. Nothing happens.

    Can you upload a workbook with that VBA?

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    See the attached workbook.

    No, conditional formatting is not required to highlight the row/column selected. As I said Cond. Formats do tend to slow things down and there were a lot in your original workbook.

    You may have other cells which require CF's but try to minimise them.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    I have tried it now, but when I try to mark up some rows to change the color, I'm getting this error.

    Still, I feel that the response is not fast enough. I work very much back and forward so I use the arrow keys a lot.
    If I press the right arrow key and intend to fast forward to the right side of the sheet and then releases, the page won't stop but continues several cells to the right after I have released the arrow key.
    Attached Images Attached Images

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need another solution for this function of highlighting of rows and cells

    To allow you to select more than one cell without the macro firing you could add an initial line of code

    If Target.Cells.Count > 1 Then Exit Sub

    However I fear there is little you can do to overcome the response time when scrolling with the arrow keys. It's just a fact of life that VBA needs to communicate with the sheet and there's a time overhead in doing that.

    You could use the Sheet_BeforeDoubleClick event rather than the sheet_selection event. That would only fire when you double clicked a cell

  9. #9
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    Thank you.

    I testet to add this line, still getting the debug :o
    Attached Images Attached Images

  10. #10
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    1,478

    Re: Need another solution for this function of highlighting of rows and cells

    Try to change to:
    Please Login or Register  to view this content.
    Artik

  11. #11
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    Thank you.
    But it's still not working.

    You can see I am marking some cells and the debug appears.
    Attached Images Attached Images

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need another solution for this function of highlighting of rows and cells

    Place the
    If Target.Cells.Count > 1 Then Exit Sub

    before the

    If Target = Range("A1") Then Exit Sub

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    ThisWorkbook module

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 04-20-2022 at 04:27 PM.
    Ben Van Johnson

  14. #14
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    Thank you very much.

    I can see this solution contains 2 conditional formatting formulas. I tried to change the color from blue to gray in the formatting rule, but that doesn't work.
    Must the color be changed in the VBA?

    And the marking of columns and rows are going forever and maybe takes up extra memory, how to set limits for how long the highlight will work?

    After I activate the macro, the blue cross disappears.

    Regards
    Attached Images Attached Images
    Last edited by Tirrazo; 04-21-2022 at 08:35 AM.

  15. #15
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    Some slight mods and additions,
    For grey highlight the color enum needs an additional item:
    Please Login or Register  to view this content.
    Modified the row/column enum to add option to highlight only the cells ABOVE the active cell and only the cells to the LEFT of the active cell. Also changed to more descriptive names.
    Please Login or Register  to view this content.


    To select the cells to be highlighted, delete the highlighted code as below press the period key and select from the dropdown that appears

    HighLightSpan = Span.RowToCellColToCell


    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,

    Thank you for this.
    I have problems making the macro work.
    As soon as I activate the macro, the highlighting is gone. Like the VBA stops working.


    Regards

  17. #17
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by Richard Buttrey View Post
    Place the
    If Target.Cells.Count > 1 Then Exit Sub

    before the

    If Target = Range("A1") Then Exit Sub

    I am trying to copy the VBA to another sheet and another workbbok. But when I save the VBA after pasting the code, it wont work.

  18. #18
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by Tirrazo View Post
    Hi, ...
    I have problems making the macro work.
    As soon as I activate the macro, the highlighting is gone. Like the VBA stops working. ...
    I don't know who you are responding to.
    If regarding my code, then I don't know what "... activate the macro..." means. You don't activate the macro, it's auto-triggered anytime you select a new cell on any sheet. The code does not belong in the worksheet modules. You should have copy/pasted it to the THISWORKBOOK module.

  19. #19
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by protonLeah View Post
    I don't know who you are responding to.
    If regarding my code, then I don't know what "... activate the macro..." means. You don't activate the macro, it's auto-triggered anytime you select a new cell on any sheet. The code does not belong in the worksheet modules. You should have copy/pasted it to the THISWORKBOOK module.
    Hi,

    I am sorry, should have replied with quote.

    Excel has this yellow warning whenever I open a Macro enabled workbook.
    It says the macro is deactivated and I have to press the "activate" button for the macro to work.
    Attached Images Attached Images

  20. #20
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    Try:
    File
    * Options
    ** Trust Center
    *** Trust Center Settings > Macro Settings
    **** Enable all macros & Trust access to the VBA project object model

  21. #21
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by protonLeah View Post
    Try:
    File
    * Options
    ** Trust Center
    *** Trust Center Settings > Macro Settings
    **** Enable all macros & Trust access to the VBA project object model
    Thank you.
    Now I don't have to activate it, but it still says the file is unsafe.

    But now I got this error, see attached photo.
    After I open the file and move the marker, the highlighting disappear. Like there is something that disables that function to work.
    Attached Images Attached Images

  22. #22
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    I can't duplicate the error with the workbook I posted; and, I don't know how your actual workbook is different from it. It even worked if more than one cell is selected if I comment out the first line (...countlarge...)

  23. #23
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by protonLeah View Post
    I can't duplicate the error with the workbook I posted; and, I don't know how your actual workbook is different from it. It even worked if more than one cell is selected if I comment out the first line (...countlarge...)
    Could you try to implement the code in this workbook and see if it works better for me?

    Could you also set the highlight to vertically go down to row 300 and horisontally go right to column ED.
    Attached Files Attached Files

  24. #24
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    The workbook you attached in post #23 has the Worksheet_SelectionChange code:

    Please Login or Register  to view this content.
    It does not contain the code that I provided, so I don't know if you want to use it or not. However, you can't/shouldn't have both the
    Worksheet_SelectionChange code and the Workbook_SheetSelectionChange code trying to operate on the same sheet as is.


    Notice that A1 is empty when the worksheet is first activated. If you select a cell that's also empty, the code exits. However, if you select any cell with data, the line:
    With Range(Range("A1"))
    is executed. This line expects A1 to contain the address of the last selected cell but A1 is empty so the line is evaluated as: With Range(""). Resulting in an error crash.

    You may modify the code as such:
    Please Login or Register  to view this content.



    My code has the option of entire row/entire column and I don't think there would be any advantage to limit the rows to 300 and the columns to ??
    Last edited by protonLeah; 04-24-2022 at 06:00 PM.

  25. #25
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by protonLeah View Post
    The workbook you attached in post #23 has the Worksheet_SelectionChange code:

    Please Login or Register  to view this content.
    It does not contain the code that I provided, so I don't know if you want to use it or not. However, you can't/shouldn't have both the
    Worksheet_SelectionChange code and the Workbook_SheetSelectionChange code trying to operate on the same sheet as is.


    Notice that A1 is empty when the worksheet is first activated. If you select a cell that's also empty, the code exits. However, if you select any cell with data, the line:
    With Range(Range("A1"))
    is executed. This line expects A1 to contain the address of the last selected cell but A1 is empty so the line is evaluated as: With Range(""). Resulting in an error crash.

    You may modify the code as such:
    Please Login or Register  to view this content.



    My code has the option of entire row/entire column and I don't think there would be any advantage to limit the rows to 300 and the columns to ??
    Thank you very much for the response.

    Your code is very complex and I would like to try out both versions.

    What is the different about Worksheet and Workbook selection change?

    The workbook you sent to me, the last one: "highlight active row and or column (1)"
    It's highlighting only a small area. This could be extended to a larger area.

    Attached also snippet where you see the yellow marked text comes after the debug message comes.



    Please Login or Register  to view this content.
    I know this is not your code, but it seems to work and not bring any slowdown. The only problem is that it removes every color a cell might have in my sheet.
    Is that possible to getaround?
    Attached Images Attached Images
    Last edited by Tirrazo; 04-25-2022 at 06:40 AM.

  26. #26
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Hi,


    Kindly see attached document.

    I got a short VBA code that does what I need it to do (highlight columns and rows after where I move in the sheet).

    The only problem: I can't use any colors because the code destroys them.

    How to solve this?


    The original spreadsheet contains different colors that each refers to a part of a project.
    Since the sheet will be loaded by numbers, it's difficult to see which row I am at.
    The numbers are gone because it's confidential.


    Regards.
    Attached Files Attached Files

  27. #27
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need another solution for this function of highlighting of rows and cells

    ...for the record this was originally posted as a new thread. In that thread which is now closed I added

    Your request seems contradictory. On the one hand you want the whole of a column and row to be highlighted as you move, yet you also want to paint a range of cells with a colour and have that colour remain even when you move to a cell in the previously coloured range. The grey highlighting is just another colour.

    The only way I could conceive of doing this is to have another test in the macro to determine if any of the cells in a column you move to are already coloured, and in which case exit the macro. However I'll leave you to decide whether that's the sort of functionality you want and comment here.
    Last edited by Richard Buttrey; 04-27-2022 at 03:03 PM.

  28. #28
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    There was no more replies so I thought people got lost in the communication. So I created a new thread with the code I felt worked best as a start.

    Could you have another look with my comments taken in consideration?

    I can send it to you by someof the numbers and the sections of colors, but I have to take away some of the numbers.

    The point for me was if It worked in this clean version it would work in the original one.

    The highlighting as a cross of column/row will be a tool to better show me where I'm at when going sideways since I've frozen a part of it at the left side.

  29. #29
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by Tirrazo View Post
    There was no more replies so I thought people got lost in the communication. So I created a new thread with the code I felt worked best as a start.

    Could you have another look with my comments taken in consideration?

    I can send it to you by someof the numbers and the sections of colors, but I have to take away some of the numbers.

    The point for me was if It worked in this clean version it would work in the original one.

    The highlighting as a cross of column/row will be a tool to better show me where I'm at when going sideways since I've frozen a part of it at the left side.
    That doesn't actually indicate whether the potential solution I suggested would be of any use. i.e. any coloured cells in a row or column would to which you moved wouldn't have the column/row highlighted.

    There's also the potential effect of the macro having to check each cell in a row/column to see if it's coloured. The original problem you were trying to address was the slowness. The more VBA code you have which has to interrogate each cell the slower will the system become.

  30. #30
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    Modified from post #3. Put in the Thisworkbook module. Do not copy to any sheet modules...


    Please Login or Register  to view this content.
    Last edited by protonLeah; 04-27-2022 at 11:34 PM.

  31. #31
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by protonLeah View Post
    Modified from post #3. Put in the Thisworkbook module. Do not copy to any sheet modules...


    Please Login or Register  to view this content.
    Hi,
    Thank you!

    I posted it in thisworkbook but nothing happens ;/
    Attached Images Attached Images
    Last edited by Tirrazo; 04-28-2022 at 12:34 PM.

  32. #32
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,847

    Re: Need another solution for this function of highlighting of rows and cells

    Well, I don't know what else I can do. It works on the sample wb you provided in the range you specified in post #3, i.e., D9:DV512

  33. #33
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Quote Originally Posted by protonLeah View Post
    Well, I don't know what else I can do. It works on the sample wb you provided in the range you specified in post #3, i.e., D9:DV512
    Thank you very much!

    I have now figured out that there is a problem with my Excel itself;

    My normal computer has Excel version: 2203 The VBA highlights row/column immediately after opening. As soon as I move from the cell you saved the sheet, the highligh is gone and don't come back.
    Second computer Excel version: 2204 The VBA highlights row/column immediately after opening. As soon as I move from the cell you saved the sheet, the highligh is gone and don't come back.
    Work PC Excel version: 2102 The VBA highlighting works fine inside the area you defined.

    So what is the problem with Excel that doesn't let me even try out the workbook?


    Regards

  34. #34
    Forum Contributor
    Join Date
    07-01-2019
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Need another solution for this function of highlighting of rows and cells

    Is it possible instead of highlighting the row and column to make the VBA draw a horisontal/vertical line from where the marked cell is?

    I don't know how to revert my excel version and I am struggling to make some of the VBA's work with the highlighting function.

    Should I reinstall office and try again, can something block the VBA from doing it's purpose?
    I am getting no error message, it just doesn't highlight anything at all.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need another solution for this function of highlighting of rows and cells
    By Tirrazo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-16-2022, 06:50 AM
  2. Function/solution to fill in cells!
    By QuantEdge in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-25-2018, 12:08 PM
  3. Replies: 3
    Last Post: 12-09-2015, 02:05 PM
  4. Highlighting rows and cells as you navigate??
    By kirbychos in forum Excel General
    Replies: 3
    Last Post: 03-12-2015, 09:53 AM
  5. [SOLVED] Highlighting rows with macro/function
    By xanthom in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-17-2014, 08:18 AM
  6. Comparing Two Rows And Highlighting Different Cells
    By ExcelNewbieSK in forum Excel General
    Replies: 6
    Last Post: 01-18-2013, 03:13 PM
  7. Highlighting rows or cells
    By CP in forum Excel General
    Replies: 5
    Last Post: 05-24-2005, 06:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1