+ Reply to Thread
Results 1 to 7 of 7

How to format an entire row based on one cell's value

  1. #1
    Registered User
    Join Date
    02-06-2010
    Location
    Brasília
    MS-Off Ver
    Excel 2007
    Posts
    23

    How to format an entire row based on one cell's value

    Hi, I have some tabular data on one spreadsheet in Excel 2016. One of the fields (columns, G in that case) is labeled "Active?", and the possible values are "Yes" and "No". I've created some conditional formatting rules to highlight a particular cell (A2, which is in the same row as G2) using a formula: =G2="No" (If G2 has a value of a literal "No"), then apply some formatting to A2. The idea is to turn the font color to grey on every cell in that row if the value in G2 is "No". However, I'd have to perform the same tedious job for cells B2, C2, D2...H2. This is not only tedious, it's also inflexible.

    Is there a smarter way?

    Thanks!

    p.s. Sorry but I tried searching prior to posting, but the results page was taking abnormally long to load.

  2. #2
    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: How to format an entire row based on one cell's value

    Quote Originally Posted by Rinocerinho View Post
    Is there a smarter way?
    Most definitely

    1. go into the CF window and in the "Applies To" box, select that entire range.
    - If this will be applying to more than just that row, highlight the other rows
    2. Change that rule to =$G2="No"
    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

  3. #3
    Registered User
    Join Date
    02-06-2010
    Location
    Brasília
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: How to format an entire row based on one cell's value

    Thanks FDibbins for your kind answer!

    But here is what's happening:

    http://imgur.com/a/yN8sU

    As you can see, the idea is to repeat that rule row by row, but not cell by cell, which would be, obviously, tedious.

    My Office is in Portuguese but I guess you can see what's going on. I created the rule for row 2, then used the format painter to repeat it to 3 (and carry on the logic to have that entire row become grey if G3="No".) But I couldn't have the row become grey after applying the rule, I don't know why. What am I missing?

    I've attached the workbook for your convenience.
    Attached Files Attached Files

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

    Re: How to format an entire row based on one cell's value

    One formula:

    =$G2="No"
    Applies to:
    =$A$2:$H$9

    delete the other formulas
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    02-06-2010
    Location
    Brasília
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: How to format an entire row based on one cell's value

    Quote Originally Posted by protonLeah View Post
    One formula:

    =$G2="No"
    Applies to:
    =$A$2:$H$9

    delete the other formulas
    That worked perfectly, protonLeah! Thanks!!

  6. #6
    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: How to format an entire row based on one cell's value

    Looks like Ben finished it off for you

  7. #7
    Registered User
    Join Date
    02-06-2010
    Location
    Brasília
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: How to format an entire row based on one cell's value

    Quote Originally Posted by FDibbins View Post
    Looks like Ben finished it off for you
    Actually you helped a lot, I was the one not spotting the $ before the G you mentioned. Also, when you said "the entire range", the noob here was confused and thought of the row instead of the "table", "matrix" or "A2 -> H10 interval".

+ 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. Conditionally format entire row based on cell selection
    By Geogee in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-18-2015, 07:04 AM
  2. [SOLVED] Conditional Format entire row but based on a cell containing a formula
    By C_P in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2014, 10:51 AM
  3. [SOLVED] Condtional format to entire row based on two difference cell value.
    By shiva_reshs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2014, 11:02 AM
  4. Conditional Format Entire Row Based On One Cell In Row
    By Spencer in forum Excel General
    Replies: 4
    Last Post: 08-22-2011, 09:21 PM
  5. Replies: 3
    Last Post: 02-21-2011, 11:29 AM
  6. Format entire row based on cell contents
    By Timja in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-13-2008, 11:54 AM
  7. [SOLVED] Can I format an entire row based on the value of a single cell?
    By LTShelley in forum Excel General
    Replies: 1
    Last Post: 08-11-2005, 03:05 PM

Tags for this Thread

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