+ Reply to Thread
Results 1 to 11 of 11

Highlight row based on cell value and weeknumber

Hybrid View

  1. #1
    Registered User
    Join Date
    07-16-2015
    Location
    Nederland
    MS-Off Ver
    2010
    Posts
    33

    Highlight row based on cell value and weeknumber

    I would like to highlight the object cell when there is an value in the the same row and week is highlighted.

    using =C$2=WEEKNUMMER(VANDAAG()) to highlight week.

    Example:

    objects 3, 7 and 10 should be also higlighted because they have a value in the active week

    something like: when contains value and in active week (column) then highlight cell in column "B" row x
    Attached Files Attached Files
    Last edited by Milcoi; 02-28-2017 at 06:31 AM.

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Highlight row based on cell value and weeknumber

    Try this:

    Formula: copy to clipboard
    =INDIRECT("R"&ROW()&"C"&MATCH(WEEKNUM(TODAY()),$2:$2,0),0)<>""


    In your attachment the other formula was showing:
    Formula: copy to clipboard
    =B$2=WEEKNUM(TODAY())

    instead of:
    Formula: copy to clipboard
    =C$2=WEEKNUM(TODAY())
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,416

    Re: Highlight row based on cell value and weeknumber

    There was a mistake in your CF formula. This is week 9. See file fixed up.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  4. #4
    Registered User
    Join Date
    07-16-2015
    Location
    Nederland
    MS-Off Ver
    2010
    Posts
    33

    Re: Highlight row based on cell value and weeknumber

    It will do nothing... see attached file
    I must have done something wrong.

    I wonder how spitfire's sollution should work
    Attached Files Attached Files
    Last edited by Milcoi; 02-28-2017 at 08:45 AM.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,416

    Re: Highlight row based on cell value and weeknumber

    If you look, you will see that I took a totally different approach to the problem.... I didn't see spitfire's solution until after I had posted.

  6. #6
    Registered User
    Join Date
    07-16-2015
    Location
    Nederland
    MS-Off Ver
    2010
    Posts
    33

    Re: Highlight row based on cell value and weeknumber

    I wonder how spitfire's sollution should work in my example.

    Quick question I have now:

    how to mark entire row in Glenn's solution?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,416

    Re: Highlight row based on cell value and weeknumber

    Apply the formula to the entire range. I'm away from my PC. I'm 6 time zones away from you and my dinner has just reached my table. If you're not fixed up by then, I'll be back in 12 hours.
    Last edited by Glenn Kennedy; 02-28-2017 at 09:00 PM.

  8. #8
    Registered User
    Join Date
    07-16-2015
    Location
    Nederland
    MS-Off Ver
    2010
    Posts
    33

    Re: Highlight row based on cell value and weeknumber

    I did not succeed, so please help.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,732

    Re: Highlight row based on cell value and weeknumber

    If I am understanding correctly open the file that Glen sent you, select cell B3, open Conditional Formatting and modify the formula for the conditional formatting rule so that it reads:
    Formula: copy to clipboard
    =ISNUMBER(1/(1/INDEX($C$3:$I$14,MATCH($B3,$B$3:$B$14,0),MATCH(WEEKNUM(TODAY()),$C$2:$I$2,0))))
    Notice that you just need to place the cursor on B3 and press the F4 key twice.
    Next change the 'Applies to:' range from =$B$3:$B$14 to =$B$3:$I$14
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Highlight row based on cell value and weeknumber

    It worked for me?!?!
    Attached Files Attached Files

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,416

    Re: Highlight row based on cell value and weeknumber

    Here's mine back again, applied to the whole row as described by JetMc.
    Attached Files Attached Files

+ 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. Highlight part of cell based off another
    By earthandbody in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2016, 07:50 AM
  2. [SOLVED] cumulatively count row based on weeknumber and cell value
    By Gjanus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2016, 11:31 AM
  3. [SOLVED] Highlight cell based on total quantity value based on repeated order numbers
    By PWilson0727 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2014, 02:02 PM
  4. [SOLVED] Highlight cell based on selected cell - Code required
    By SharpL in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-24-2014, 09:48 AM
  5. Replies: 1
    Last Post: 01-20-2014, 03:52 AM
  6. [SOLVED] Add Weeknumber value to cell depending on the date in a cell 4 columns away
    By Roxner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2013, 02:37 PM
  7. Highlight a Row based on a cell
    By webstar in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-16-2007, 01:50 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