+ Reply to Thread
Results 1 to 7 of 7

Format cell based on value(s) in separate column

  1. #1
    Registered User
    Join Date
    11-28-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Format cell based on value(s) in separate column

    Hi all

    I'm creating a workbook to be used for monitoring test scripts.
    My issue is that I need to fill one group of merged cells (is six cells merged which is a script status indicator and should display the word PASS or FAIL) with a colour reflecting the status.
    Column E is the Pass/Fail indicator for each test step and can only contain 'Pass' or 'Fail' and has a fixed number of rows (ie the number of test steps)

    – If any cell in E contains 'Fail' I would like my merged cell to turn red and say 'Fail'
    - If all cells in the rows used in E have 'Pass' I would like it to be green and say 'Pass'
    - if all cells in E below the limit of the completable number of rows in E have 'Pass' I'd like it to stay white and say 'Incomplete'

    Any help with this would be most appreciated

    Thanks

    Will

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Format cell based on value(s) in separate column

    Hi Will,

    Any chance you could upload your workbook, anonymised if necessary?

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-28-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Format cell based on value(s) in separate column

    No problem - here you go.

    Thanks

    Will
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-28-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Format cell based on value(s) in separate column

    Hi Richard,

    Have you had time to garner any thoughts on that at all?

    Cheers

    Will

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Format cell based on value(s) in separate column

    hi Will, please check attachment, try to change value in column E.

    PS. If nothing happens try to run the following code first:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-28-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Format cell based on value(s) in separate column

    Brilliant! Thanks very much sir, that's great. What was the solution? (I have a good 20 such workbooks with multiple worksheets to use this in).

    Thanks again

    Will

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Format cell based on value(s) in separate column

    the following code is in the worksheet module (right click sheet tab, view code, paste the whole code)

    Please Login or Register  to view this content.

+ 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