+ Reply to Thread
Results 1 to 4 of 4

Copy fill color of Adjacent Cell

  1. #1
    Registered User
    Join Date
    10-22-2013
    Location
    Auburn Alabam
    MS-Off Ver
    Excel 2013
    Posts
    11

    Copy fill color of Adjacent Cell

    Hello all, first post
    I have looked in the forum for an answer to this question, and I believe it should be simple but can't find anything that quite fits my problem.
    I am setting up a schedule maker for a coworker, in which I have divided two columns into time in and time out for each day of the week. My coworker wants the cell to be highlighted red if the shift is a night shift (Begins after 4), and I used conditional formatting to set up both columns independently to evaluate if the value of their content is after 4 PM. However, this means if a shift starts at noon, but ends after 4, only the time out cell will be highlighted red, not a huge issue but it isn't aesthetically pleasing. Any help is appreciated,
    Thanks

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

    Re: Copy fill color of Adjacent Cell

    So, in the case of a 12-noon start, does that mean that you do not want the end time to show red? If so, you should amend the CF condition which acts upon the time out column, so that it uses the time in value. This is easier to do using a formula like:

    =A2>TIME(16,0,0)

    while the cell B2 is selected, assuming your times are in columns A and B.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-22-2013
    Location
    Auburn Alabam
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Copy fill color of Adjacent Cell

    I think I understand what you are saying, would that be a conditional format I could paint onto other cells and have them use their adjacent time "in" cell? It would be quite tedious to have to set each ones conditional formatting individually, as there are dozens of employees each with seven cells for the day of the week.
    Thanks

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Copy fill color of Adjacent Cell

    Conditional Formatting works well over a range of cells so you don't have to have one rule per cell. For your example, select the column for the IN time and create the rule...all cells that match the rule will be formatted accordingly. Do the same for the OUT column.

    You are not "painting" the format. the format is calculated according to the criteria set in the Conditional Formatting.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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] Macro: Use cell fill color to determine if to copy & paste to another cell on the same row
    By tsamuels in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-19-2013, 09:50 AM
  2. Change Font Color Based On Adjacent Cell Color
    By Zorro9758 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2013, 02:47 PM
  3. Filling adjacent cells with same fill color
    By watido88 in forum Excel General
    Replies: 3
    Last Post: 01-02-2013, 06:43 PM
  4. [SOLVED] Copy a row to another sheet based on cell fill color
    By swatkins1977 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-22-2012, 02:55 PM
  5. Setting Color of Range based on adjacent cell color
    By JimE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-02-2009, 09:04 PM

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