+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting Across Multiple Columns, But Only Some Columns Turn Color

  1. #1
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    204

    Conditional Formatting Across Multiple Columns, But Only Some Columns Turn Color

    I've used conditional formatting many times and this is the first time it hasn't worked as I expected.

    I have three conditional colors that I want to apply across a row, from columns B to F, and I want to apply this coloring one row at a time to rows 15 to 145. I have a formula to determine which rows should, and should not, be colored.

    The good news is that color appears on every row that should have color, and does not appear on every row that should not have color.

    The bad news is that the color appears in only one column of each row that should be colored and not all of the columns from B to F. Two of the conditional coloring commands color only cells in column 'B' and one conditional coloring command colors only cells in column 'F'. However, to repeat, the proper color always appears in the proper row.

    I used =$B$15:$F$145 to designate the area to which the formulas for conditional coloring should be applied.

    Does anyone have an idea of what I've done wrong?

    This has always worked fine before, so I'm very puzzled.
    Attached Files Attached Files
    Last edited by lovecolorado; 03-13-2019 at 06:05 PM. Reason: Add a file, per the request in a response

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,306

    Re: Conditional Formatting Across Multiple Columns, But Only Some Columns Turn Color

    What formula did you use?


    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    204

    Re: Conditional Formatting Across Multiple Columns, But Only Some Columns Turn Color

    I added a desensitized file, per TMS's request.

    This file acts, in most ways, the same as my original file, with one exception - a new problem. One of the conditional formatting colors is applied to an entire column, irrespective of whether color should have been applied to a specific row. Previously, every row had, or did not have, the proper color, with the problem being that the color was not applied across the row to columns 'B' to 'F'.

    However, the symptoms are similar enough that I suspect any solution to this problem will work on my original file.

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Conditional Formatting Across Multiple Columns, But Only Some Columns Turn Color

    The issue appears to be in the conditional formatting formulas. You are not keeping the referenced columns absolute with $, so the comparisons are not working as you'd expect. For your "green" format, this is your formula:
    Please Login or Register  to view this content.
    I changed it to this, and it works:
    Please Login or Register  to view this content.
    A similar change can be made to the "pink" formula. I recommend moving the green formula to the top of the conditional format rule list, and checking Stop if true checkbox for it. Your purple formula references column XFB throughout, so I'm really not sure what you're expecting there.

  5. #5
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    204

    Re: Conditional Formatting Across Multiple Columns, But Only Some Columns Turn Color

    Thank you! It took me a stupid long-time to find all of the missing $ in the formula, but once I understood what was missing, it worked perfectly. This is a lesson I will NOT forget.

  6. #6
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    204

    Re: Conditional Formatting Across Multiple Columns, But Only Some Columns Turn Color

    Melvosh has identified the problem. The conditional formatting works perfectly now.

+ 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] Conditional Formatting: If Date is ____ Make subsequent rows turn color
    By morerockin in forum Excel General
    Replies: 1
    Last Post: 05-20-2015, 09:23 AM
  2. [SOLVED] Conditional Formatting - Multiple Columns
    By VegasL in forum Excel General
    Replies: 3
    Last Post: 08-29-2013, 01:03 PM
  3. Using color scale conditional formatting on columns
    By moty.98 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 11:05 AM
  4. Replies: 2
    Last Post: 04-15-2013, 02:05 PM
  5. Conditional Formatting Between Multiple Columns
    By p_arab in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2013, 03:47 PM
  6. Excel 2007 : Conditional Formatting for Multiple Columns
    By AlexisDis in forum Excel General
    Replies: 3
    Last Post: 10-18-2011, 12:00 PM
  7. Conditional formatting multiple columns
    By Moebius8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2008, 10:55 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