+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting every nth cell- NOT SHADING

  1. #1
    Registered User
    Join Date
    09-20-2016
    Location
    Boston, US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Conditional Formatting every nth cell- NOT SHADING

    Hi,

    I am using excel to view values of several samples and controls. It looks something like this:
    sample 1- 67
    sample 2- 68
    sample 3- 88
    control 1- 10
    control 2- 15
    I am trying to use conditional formatting to highlight cells in a particular range.
    This problem is I need to highlight samples if they are <20, but highlight controls if they are >35.
    How can I use conditional formatting to format only every nth cell in a row?
    all i can find is =MOD(ROW(),2)=1
    but this can only be used to highlight, reguardless of the values
    -also, the list is not only 5 cells deep it is hundreds of samples and controls. Always 3 samples, always 2 controls. So i would like something that will format all of the values that fall in the range
    Last edited by voots; 09-20-2016 at 01:53 PM. Reason: clarification

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Conditional Formatting every nth cell- NOT SHADING

    Hi
    Can you clarify what is [sample 1- 67]? Is that < 20? is text in same cell?
    Please upload a sample file with a example

  3. #3
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Conditional Formatting every nth cell- NOT SHADING

    @ voots

    Assuming your page is organized as:
    Captura2.JPG

    Set 2 conditional formating rules using formula in cell D2
    and copy format down to all your data rows:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Barriers are there for those who don't want to dream

  4. #4
    Registered User
    Join Date
    09-20-2016
    Location
    Boston, US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Conditional Formatting every nth cell- NOT SHADING

    very similar, except column B is not just "sample" it is "sample#" ie "sample 1, sample 2, etc"
    i'm guessing the equation won't work with that

  5. #5
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Conditional Formatting every nth cell- NOT SHADING

    Then you have to extract the 6 first letters form the line:
    sample
    contro.....( not control)



    Please Login or Register  to view this content.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting every nth cell- NOT SHADING

    Can you post a SMALL sample file and show us what result you expect?

    A SMALL file will have about 20 rows worth of data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] Formula or Conditional formatting for cell shading
    By Jhertilus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-15-2014, 01:31 PM
  2. Conditional Formatting on the Basis of Cell Shading
    By bykareem in forum Excel General
    Replies: 1
    Last Post: 12-03-2013, 02:43 AM
  3. [SOLVED] Conditional Formatting Shading Rows Based On A Single Cell
    By Kanook22 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-17-2012, 12:09 PM
  4. How can you override cell shading using Conditional formatting?
    By mrgillus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2009, 11:46 AM
  5. [SOLVED] Conditional Cell Shading (based on the shading of other cells)
    By Tubby in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2006, 05:10 PM
  6. how do I use conditional formatting for alternating cell shading?
    By Exceluser in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2006, 12:30 AM
  7. [SOLVED] conditional formatting-shading
    By Shooter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-23-2005, 05:06 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