Closed Thread
Results 1 to 7 of 7

Conditional Formatting color banding based on column contents

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    Conditional Formatting color banding based on column contents

    Column B contains a category name

    Is there a way to write a formula for conditional formatting that looks at the contents of cell B(row whatever) and if it is equal to the cell above it, then color band it the same, if not alternate.

    This would be the result:
    Row Format….Column B
    Green band…..Category 1
    Green band…..Category 1
    Green band…..Category 1

    White band…..Category 2
    White band…..Category 2
    Green band…..Category 3
    Green band…..Category 3
    Green band…..Category 3
    Green band…..Category 3
    Green band…..Category 3

    White band…..Category 4
    Green band…..Category 5
    Green band…..Category 5

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Are you looking for the conditional formatting formula ...?
    Formula Is
    Please Login or Register  to view this content.
    to be copied to all cells of row 1 ...
    HTH
    Carim


    Top Excel Links

  3. #3
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    It needs to evaluate the cell in Column B as compared to the cell above it.
    I can't place =category 1, 2, 3, 4 etc into hundred of cells (with dozens of categories).

    I'm looking to band this entire worksheet the way that this formula
    =MOD(ROW()-1,3*2)+1<=3
    bands every other group of 3 rows (like ledger paper) on the entire worksheet.
    But rather than banding in groups of three I want to visually separate each category from the previous by banding.

    See the attached example of what I want vs standard 3 band
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    Since it doesn't look like many people want to view the spreadsheet, here is a .pdf of what I'm looking for.

    Thanks
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    If anyone's interested...
    I found a solution here:
    http://www.officekb.com/Uwe/Forum.as...changes-in-col

    in a nutshell
    If the column to be checked is B

    Create a helper column, say in column A
    A1: 0
    A2: =MOD(OFFSET($A2,-1,0)+(OFFSET($B2,-1,0)<>$B2),2)

    Then set up the Conditional Formatting
    Select any cell on Row 1, then select the entire worksheet

    Format, Conditional Formatting
    condition 1, formulas is: =$A1=1


    personally, I think this visual separation makes a huge impact.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-12-2008
    Location
    Boston
    Posts
    1

    Thanks

    For hours I have been looking for a solution to this problem. I am going to give your solution a whirl and I will let you know how it works out for me. Many many thanks. I would have thought more folks would be interested in this type of banding scenario.

  7. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

Closed 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