+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting Formula to Color Rows Based on Column Value

  1. #1
    SteveC
    Guest

    Conditional Formatting Formula to Color Rows Based on Column Value

    I've tried, but I can't figure it out. How to use conditional formatting to
    alternatively color rows based on values in a column?

    For example, in B13:B3000, I have values like this:

    Col B Row color I'd lilke
    Apples Green
    Apples Green
    Pears White
    Pears White
    Pears White
    Pears White
    Bananas Green
    Monkeys White
    Monkeys White
    Giraffes Green etc...

    Thanks for your help...

  2. #2
    SteveC
    Guest

    RE: Conditional Formatting Formula to Color Rows Based on Column Value

    http://www.mvps.org/dmcritchie/excel/condfmt.htm

    Found the answer --> you need a helper column, unfortunately...

    "SteveC" wrote:

    > I've tried, but I can't figure it out. How to use conditional formatting to
    > alternatively color rows based on values in a column?
    >
    > For example, in B13:B3000, I have values like this:
    >
    > Col B Row color I'd lilke
    > Apples Green
    > Apples Green
    > Pears White
    > Pears White
    > Pears White
    > Pears White
    > Bananas Green
    > Monkeys White
    > Monkeys White
    > Giraffes Green etc...
    >
    > Thanks for your help...


  3. #3
    Gary L Brown
    Guest

    RE: Conditional Formatting Formula to Color Rows Based on Column Value

    2 ways of doing this.
    1) Use a 'cheat' column and conditional formatting.
    Advantage: If the data changes, the formatting changes.
    Disadvantage: Having an extra column with a formula
    2) Use a macro.
    Advantage: No extra columns or formulas to maintain.
    Disadvantage: If the data changes, the formatting doesn't change unless
    the macro is re-run.

    Using option 1...
    - Insert a blank column in Column A
    - In Cell A1, put the number 1
    - in Cell A2, put the formula...
    =IF(C2=C1,A1,A1*-1)
    It is comparing Apples to Pears to Bananas to Monkeys to find out when the
    data changes. The value in column A will change from 1 to -1 everytime the
    data switches from apples to pears, etc.
    - Copy the formula in A2 down to the bottom of the data.
    - Create the conditional formatting...
    - highlight the entire worksheet
    - Format>Conditional Formatting...
    - Condition 1: Formula is
    =$A1:$A1=1
    - Format>Patterns>Green
    - All rows that have a '1' in Column A will now turn green.

    HTH,
    --
    Gary Brown
    gary_brown@ge_NOSPAM.com
    If this post was helpful, please click the ''Yes'' button next to ''Was this
    Post Helpfull to you?''.


    "SteveC" wrote:

    > I've tried, but I can't figure it out. How to use conditional formatting to
    > alternatively color rows based on values in a column?
    >
    > For example, in B13:B3000, I have values like this:
    >
    > Col B Row color I'd lilke
    > Apples Green
    > Apples Green
    > Pears White
    > Pears White
    > Pears White
    > Pears White
    > Bananas Green
    > Monkeys White
    > Monkeys White
    > Giraffes Green etc...
    >
    > Thanks for your help...


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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