+ Reply to Thread
Results 1 to 5 of 5

Highlight or colour a cell or cells based on cells in another range.

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Highlight or colour a cell or cells based on cells in another range.

    I am trying to do a seating plan and wonder if anyone can help with showing when a seat has been filled?

    Guests Worksheet = Guests and the seat numbers they have been allocated.

    Seating Worksheet = Numbered seating plan. (In the final version all of the seats will be numbered.

    When seat numbers are entered into the "guest worksheet" how can the corresponding number in the "seating worksheet" be coloured e.g. when the number 16 is entered in the guest worksheet how can seat number 16 be turned into another colour to show that a seat has been allocated.

    VBA code scares me and other newbies will be using the worksheet so can this be done with excel formulas?

    Thank you.
    Attached Files Attached Files

  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,604

    Re: Highlight or colour a cell or cells based on cells in another range.

    I think you need to number your seats with a unique reference, eg the row number and the seat number - I've put a few examples in row 5 of the attached file. I've also applied a named range "Seats" to columns E and F of your First Show sheet, and put some fictitious data in there which follows the numbering convention.

    In the Seating sheet I've applied conditional formatting to the cells C2:L2 using this formula:

    =COUNTIF(Seats,C2)>0

    which turns the background red and foreground white (and bold) if the seat number appears in the Seats columns. You need to extend this to the rest of your sheet.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Highlight or colour a cell or cells based on cells in another range.

    This uses conditional formatting and named ranges.
    the first seat number column is named seatlist1, the other, seatlist2.
    Attached Files Attached Files
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    10-30-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Highlight or colour a cell or cells based on cells in another range.

    Totally Amazing. Thank you both very much indeed. I appreciate the guru precious time you have both taken to help me. If that sounds too grateful it's because I am.

    Pete_UK
    The seat numbering with the row number included is a brill idea. The noobs might lose a few brain cells. I can already picture trying to convince a couple of airheads that it's a great idea. The formula works perfectly so I'll do a refresher with COUNTIF.

    protonLeah
    Again the formula works perfectly. Thanks for doing the seat numbering to show the conditional formatting example fully. It will really help.

    I'm going to try both of these methods over the next day or so and let you know how it goes. Hope you don't mind if I pop back to ask any questions if my brain cells start frying as well.

    Thanks again.

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

    Re: Highlight or colour a cell or cells based on cells in another range.

    I think the important thing with the seat numbering is that each seat number must be unique - you could just have a sequential number from 1 to 500 (or whatever), or you could have a row letter with a number, or row number with a number (as in my example). Set this up in your seating plan, and then make sure the ticket numbers reflect your seat numbers (and the entries in Seats columns also follow the same scheme).

    Feel free to post back with any future problems. You might want to mark this thread as Solved (the FAQ describes how), and then any future questions could be in a new thread.

    Hope this helps.

    Pete

+ 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