+ Reply to Thread
Results 1 to 3 of 3

Formula to change cells base on staff allocation

  1. 09-19-2017, 10:54 PM #1
    johnso1985
    johnso1985 is offline
    Registered User
    Join Date
    09-11-2013
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    13

    Formula to change cells base on staff allocation

    Hello

    I am setting up a small staff spreadsheet to keep track of our job allocation.

    I enter a series of data into a row and allocate this to a staff member by entering there initials.

    I would like this entire row to change color based on the said staff members initials to allow each staff member to see each job based on colour

    Can someone help me write this formula as I am a beginner

    I have attached a screenshot of the base spreadsheet, for example every time I enter JD withing "allocated to" I would like to change the row colour to green, every time I enter ES withing "allocated to" I would like to change the row colour to blue etc
    Untitled.jpg
    Reply With QuoteRegister To Reply
  2. 09-19-2017, 11:08 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Formula to change cells base on staff allocation

    Hi, welcome to the forum

    You would use Conditional Formatting for this, find it on the Home Tab under Styles/Conditional Formatting. You would need to set up a new rule for each person you need to apply this to.

    Also, please upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 09-19-2017, 11:10 PM #3
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula to change cells base on staff allocation

    hey johnso1985. not sure what you mean by allocated to "I". but to highlight the row when column G shows JD:
    select the range you want to apply to (say from A4:J10)
    in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
    in Excel 2003, go to Format -> Conditional Formatting -> Formula is:
    =$G4="JD"

    Format the colors desired. repeat the steps for ES

    ps: noticed you have not marked other threads you created as Solved.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help With Formula To Lock Or Unlock Cells Base On Their COntent
    By Barbara Excel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2016, 12:19 PM
  2. linking employee task list to general an office staff allocation chart
    By TreeToes83 in forum Excel General
    Replies: 13
    Last Post: 08-04-2015, 09:00 AM
  3. [SOLVED] Fill cells base on some IF and MATCH formula
    By kepasso in forum Excel General
    Replies: 3
    Last Post: 01-09-2015, 03:34 AM
  4. [SOLVED] ? Variable Ranges ? how to change address in Formula range base on another cell value ?
    By DrAlloway in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-27-2014, 07:34 PM
  5. Allocation of Data to Staff with Certain Criteria
    By Wahajulsamad in forum Excel General
    Replies: 12
    Last Post: 01-05-2014, 02:08 AM
  6. Allocation of a a fixed staff list, without duplication
    By mpgates in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-10-2013, 09:58 PM
  7. Roster / Staff Allocation
    By KingAaron in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-13-2006, 09:40 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1