+ Reply to Thread
Results 1 to 12 of 12

Help with conditional formatting for an entire row based on one cells value -- SOLVED

  1. #1
    Registered User
    Join Date
    02-05-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Help with conditional formatting for an entire row based on one cells value -- SOLVED

    Hi everyone, I've got a spreadsheet that I use to track customer jobs. I'm constantly adding and modifying data in the spreadsheet. I would like to apply conditional formating for an entire row based off the date in column C. I can create a conditional format based on the date, but it only applies to column C and also applies the formatting to blank cells, which I do not want. What I'm using right now is =TODAY()-C1>30. Any suggestions?
    Last edited by kosnic; 04-17-2014 at 12:10 PM.

  2. #2
    Registered User
    Join Date
    01-09-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: Help with conditional formatting for an entire row based on one cells value

    Hey mate.

    Do you have a sample workbook you can upload?

    Once you create the conditional format, in the 'Applies to' sections of the conditional format box, extend the range to the cells you wish to have highlighted. So for example:

    =$A$2:$N$200

    That range combined with the conditional format will highlight any row that meets that criteria. If that doesn't work, just upload the sample workbook (minus sensitive data) and we can go from there.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Help with conditional formatting for an entire row based on one cells value

    Change a bit your function used as a rule:
    Please Login or Register  to view this content.
    This wil prepare rule to be copied left and right
    To make it active only in rows with filled cell in column C make another modification:
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with conditional formatting for an entire row based on one cells value

    If you want to apply CF to more than 1 cell in a row, you need to use the correct rule option...

    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =and($C1<>"",TODAY()-$C1>30)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Help with conditional formatting for an entire row based on one cells value

    This small sample file might help you:
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    02-05-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Help with conditional formatting for an entire row based on one cells value

    Quote Originally Posted by Reapz View Post
    Hey mate.

    Do you have a sample workbook you can upload?

    Once you create the conditional format, in the 'Applies to' sections of the conditional format box, extend the range to the cells you wish to have highlighted. So for example:

    =$A$2:$N$200

    That range combined with the conditional format will highlight any row that meets that criteria. If that doesn't work, just upload the sample workbook (minus sensitive data) and we can go from there.

    I had tried something like that, but for some reason the only other cells it applies to that have any data in them is D, but it does format the entire row in the blank cells. I will get an example sheet uploaded.
    Last edited by kosnic; 04-17-2014 at 10:57 AM.

  7. #7
    Registered User
    Join Date
    02-05-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Help with conditional formatting for an entire row based on one cells value

    Quote Originally Posted by Kaper View Post
    Change a bit your function used as a rule:
    Please Login or Register  to view this content.
    This wil prepare rule to be copied left and right
    To make it active only in rows with filled cell in column C make another modification:
    Please Login or Register  to view this content.
    This works for me, BUT, still includes all of my blank rows.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with conditional formatting for an entire row based on one cells value

    Uoload a sample file, then we wont be guessing

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Help with conditional formatting for an entire row based on one cells value

    This might be more appropriate:

  10. #10
    Registered User
    Join Date
    02-05-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Help with conditional formatting for an entire row based on one cells value

    Quote Originally Posted by FDibbins View Post
    If you want to apply CF to more than 1 cell in a row, you need to use the correct rule option...

    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =and($C1<>"",TODAY()-$C1>30)
    I got it with this

    Formula =and($C1<>"",TODAY()-$C1>30)
    Applies to =$A:$F

    I added another formula for 15 days and just had to put the older one first in the list. Thanks for your help.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with conditional formatting for an entire row based on one cells value -- SOLVED

    Im happy you got it resolved

    I would, however, advise against using entire columns for CF, because CF can become very recourse-intensive. using entire columns will tend to slow your file down - rather, restrict the range to just what you need (or a bit more)

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Help with conditional formatting for an entire row based on one cells value -- SOLVED

    I'll second the recommendation against whole column CF formatting. It can become a resource hog.

+ 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. conditional formatting based on an entire row
    By vaeevictiss in forum Excel General
    Replies: 4
    Last Post: 10-31-2013, 05:34 PM
  2. Conditional Formatting entire rows based on data
    By wilsonmedic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-21-2012, 10:46 AM
  3. Replies: 4
    Last Post: 11-24-2011, 04:46 PM
  4. Conditional formatting an entire row based on colum A cells.
    By sararayne in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 09-09-2008, 05:51 PM
  5. HOW CAN I APPLY CONDITIONAL FORMATTING BASED TO AN ENTIRE ROW?
    By James M in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-19-2005, 11:45 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