+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting based on rows AND columns

  1. #1
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Conditional formatting based on rows AND columns

    Hi forum,
    I'd like to create a conditional formatting in my resource sheet so that cells turn red if one person is required in too many projects. See my attached sheet on how I'd like the outcome to look.
    1 Full time = OK
    2 Full times = NOK
    1-2 Part times = OK
    3 Part times NOK
    1 Full + 1 Part time = OK

    I hope someone can help. Thanks in advance.
    /Helena

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Conditional formatting based on rows AND columns

    Is this to be done on a week-by-week basis, so that Helena can't be down as working on 2 projects full-time in week1, nor can Nils be down as working on 3 projects part time in the same week?

    Pete

  3. #3
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20
    Yes, that is correct! /Helena
    Last edited by AliGW; 11-30-2017 at 02:02 AM. Reason: Unnecessary quotation removed.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional formatting based on rows AND columns

    This proposal uses helper columns that reference a small lookup table. In M1:N2 find the lookup table which assigns numeric values of 1 to Full time and 0.75 to Part time.

    Then for the helper columns array enter this formula in F2 fill across and down as far as needed.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula for Red in CF
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and for Green
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Applied in the attached.
    Dave

  5. #5
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Conditional formatting based on rows AND columns

    Thanks for your idea, I've been thinking along the same lines but didn't get it quite right.
    My problem with this solution is that there are around 60 columns in my real workbook and I think it will be a very unstable solution to have one help column per data column there. Is there a way of setting "Full time" = 1 and "Part time" = 0,75 in excel so that I can use sumif directly?
    BR, Helena

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional formatting based on rows AND columns

    I'll have to think about that. I am thinking along the lines of SUMPRODUCT or MMULT.

    In the meantime this would dispense with the helper columns, but still require the lookup table M1:N2.

    In Name Manager this named formula Full_Part
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then the CF rules become Red:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Green:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Conditional formatting based on rows AND columns

    I applied these conditional formatting formulas to the range C2:E100

    =AND(C2<>"",OR(COUNTIFS(C$2:C$100,"Full time",$A$2:$A$100,$A2)>1,COUNTIFS(C$2:C$100,"*time",$A$2:$A$100,$A2)>2))

    red format

    =SEARCH("Time",C2)

    green format

    See attached - try changing some of the values to see if it works for you

    For more columns the formulas can remain exactly the same - just change the "applies to" range in conditional formatting
    Attached Files Attached Files
    Last edited by daddylonglegs; 12-01-2017 at 08:11 PM.
    Audere est facere

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional formatting based on rows AND columns

    This is simpler than my previous in post #6 and no longer requires helper columns or lookup table.

    In the Name Manager Full_Part formula try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The CF formulas remain the same as before.

  9. #9
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Conditional formatting based on rows AND columns

    Thanks to both of you!

    Using your input I solved it with
    OR(COUNTIFS(Person;$A2;C:C;"Full time")>1;COUNTIFS(Person;$A2;C:C;"Part time")>2)
    for red formatting.
    The rule applies to range $C$2:$E:$100.
    Stop if true is checked.
    Green formatting is a lot easier with a rule of just "Cell contains" "Full time" or "Part time".

    My only remaining problem is that my sheet has become very slow in responding so I am considering a macro, but that should be posted elsewhere, unless anyone here has any tips on speeding it up.
    Thanks again,
    /Helena

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional formatting based on rows AND columns

    No tips. Just a thought.

    What is the range in "Applies to:"?

    Applied to excessively large ranges ... especially whole columns ... will slow performance.

  11. #11
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Conditional formatting based on rows AND columns

    Hi again,
    Sheet is still very slow. I apply my conditional formatting to H4:AG88 so the area is not all that big. I would be happy for any help. Or do you prefer if I start a new post since the original question was answered?
    BR,
    Helena

  12. #12
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Conditional formatting based on rows AND columns

    Sorry, here is the worksheet.
    BR,
    Helena
    Edit: Sorry again, mistakenly attached file with original information.
    Last edited by HelenaF; 01-04-2018 at 03:52 AM.

  13. #13
    Registered User
    Join Date
    10-04-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Conditional formatting based on rows AND columns

    I have removed all references to entire columns. It's working ok now but not perfect. This thread can be closed.

+ 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. Replies: 1
    Last Post: 05-23-2016, 01:53 PM
  2. Conditional Formatting based on few columns
    By VKS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2014, 04:11 AM
  3. [SOLVED] Conditional Formatting: Duplicate rows based on multiple columns.
    By Old4xford in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-08-2014, 11:25 AM
  4. Flip Rows and Columns and Conditional Formatting - Please Help
    By tracy702 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-09-2013, 08:54 AM
  5. Conditional Formatting - Multiple Rows and Columns
    By mltucc in forum Excel General
    Replies: 2
    Last Post: 04-20-2012, 10:30 PM
  6. Conditional Formatting - Multiple rows/columns
    By Phillydog in forum Excel General
    Replies: 4
    Last Post: 03-21-2011, 04:50 PM
  7. concatenate rows with columns based on conditional formatting in VBA?
    By bandit_kaine in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2010, 03:16 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