+ Reply to Thread
Results 1 to 14 of 14

Highlight a limited range on selection of a given cell

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    641

    Highlight a limited range on selection of a given cell

    I'm hoping that some one can help with this issue.

    When I chose a cell in the range C4:C300 I'd like the equivalent row From A to I to be coloured light grey.

    So , for example , if I select cell C35 , then cells A35:I35 would be coloured. The colour would cancel once I click elsewhere.

    As a complicating factor , I can't use conditional formatting as I have already used all the options.

    Grateful for any advice.

    Using Excel , Word and Access 2003 - For the whole of 2024

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Highlight a limited range on selection of a given cell

    1. Can you not release one of your existing conditional formats? Some people are a bit wasteful.

    2. I assume that you are happy to use macros.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Highlight a limited range on selection of a given cell

    Put this code in sheet module...
    Please Login or Register  to view this content.
    The colour would cancel once I click elsewhere.
    Elsewhere in col C you mean...and anywhere else on sheet...
    Attached Files Attached Files
    Last edited by sintek; 12-21-2020 at 01:39 PM. Reason: Updated File
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Forum Contributor
    Join Date
    12-08-2020
    Location
    South Korea
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Highlight a limited range on selection of a given cell

    Please Login or Register  to view this content.
    Last edited by chronjy; 12-21-2020 at 01:39 PM.

  5. #5
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    641

    Re: Highlight a limited range on selection of a given cell

    Hi All

    Thanks for your suggestions.

    This works , but doesn't cancel the colour when I click elsewhere in C or on anywhere else on the sheet. It leaves all the rows that I've selected as grey.

    Please Login or Register  to view this content.
    I found that if I leave

    Please Login or Register  to view this content.
    unremmed it colours the whole sheet cyan.

    Thanks again.


  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Highlight a limited range on selection of a given cell

    When I chose a cell in the range C4:C300 I'd like the equivalent row From A to I to be coloured light grey.
    So , for example , if I select cell C35 , then cells A35:I35 would be coloured
    From this I understand...If you click any cell in C3:C400 that row A to I is colored
    If you click again any cell in C3:C300 the other row is cleared and that new selection row A to I is colored
    If you click anywhere other than C3:C300 then all is cleared...

    That is how Post 3 code and file works...If this is not what you are after then I suggest you explain in more detail...

    unremmed it colours the whole sheet cyan.
    This has nothing to do with my code...Did you even try my sample upload?

  7. #7
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    641

    Re: Highlight a limited range on selection of a given cell

    ..If you click any cell in C3:C400 that row A to I is colored
    If you click again any cell in C3:C300 the other row is cleared and that new selection row A to I is colored
    If you click anywhere other than C3:C300 then all is cleared...
    Yes , that's exactly how it would work in the given range. No misunderstanding. My version of xl doesn't run the download you posted unfortunately. I did try.

    Thanks again for your advice.
    Last edited by CDandVinyl; 12-21-2020 at 04:04 PM.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Highlight a limited range on selection of a given cell

    Oops pre 2007....

  9. #9
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    641

    Re: Highlight a limited range on selection of a given cell

    I find this code works works well , but doesn't clear the colour when I click elsewhere in the target range or elsewhere on the sheet.

    Please Login or Register  to view this content.
    Can you suggest a way to modify this so it will clear the colour when I click away from the current cell in C , either to another cell in the sheet or another cell in C?

    Thanks.


  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Highlight a limited range on selection of a given cell

    Can you suggest a way to modify this so it will clear the colour when I click away from the current cell in C
    Like I said, for me code in post 3 does this exactly...
    or another cell in C?
    If you click any cell in C other than the required range that initially fires the code i.e C4:C300 the colour will be cleared...

    Pointless wanting to clear the color if you click within the range as this is your initial requirement...
    When I chose a cell in the range C4:C300 I'd like the equivalent row From A to I to be coloured light grey.
    How are you wanting to click in this range, have the row coloured and then click another cell in this range and not have it coloured...Defeats the initial request...

    My sample code does this...
    If you click again any cell in C4:C300 the other row is cleared and that new selection row A to I is colored
    If you click anywhere other than C4:C300...anywhere else on sheet then all is cleared...
    Perhaps someone with 2003 version can test and advise...

  11. #11
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    641

    Re: Highlight a limited range on selection of a given cell

    Ok thanks for your help. Yes , maybe it's a 2003 thing.

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Highlight a limited range on selection of a given cell

    Give this a try...
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    641

    Re: Highlight a limited range on selection of a given cell

    Yes , very good. Thanks again. Very nice of you to stick with it.


  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Highlight a limited range on selection of a given cell

    Glad You Got It Sorted
    Thanks.png

+ 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. [SOLVED] Cell Highlight Selection
    By TheGomzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-24-2019, 11:46 AM
  2. Cell Highlight Selection
    By TheGomzee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2019, 03:32 PM
  3. Limited cell selection
    By glda19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2018, 02:28 AM
  4. [SOLVED] ComboBox with limited list selection.
    By gsandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2016, 05:32 PM
  5. Replies: 5
    Last Post: 12-17-2015, 06:43 AM
  6. Cell selection highlight colour
    By Cookstein2 in forum Excel General
    Replies: 8
    Last Post: 02-12-2013, 03:55 PM
  7. Highlight cell upon selection
    By m.cain in forum Excel General
    Replies: 2
    Last Post: 04-24-2012, 05:05 PM

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