+ Reply to Thread
Results 1 to 17 of 17

Color a cell if it meets 2 to assigned values in one other sheet.

  1. #1
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Color a cell if it meets 2 to assigned values in one other sheet.

    I have created a gannt in excel.
    I have three separate sheets.
    1. Gives all information about the project and a timeline.
    2. Automatic registration of working hours (using dropdown menus)
    3. A sheet that is generated by sheet 2 and contains all working hours.

    I have made an automatic registration of working hours.
    This is generated in a separate sheet.

    Then i want a cell to change color in my first sheet in at a condition.

    Example if a date generated in row 1 and the task name is generated on the same line. Then color a cell in sheet one automatic if the conditions are met.

    Is there an easy way to do this?

    Excuse for my bad English. Not my native language

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    conditional formatting should work

    perhaps a sample sheet - with example of the results you want and why
    or can you expand and explain
    Example if a date generated in row 1 and the task name is generated on the same line. Then color a cell in sheet one automatic if the conditions are met.
    row 1 - what column on what sheet
    task name in what column , assume that just means its not blank
    colour what cell in sheet 1
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    i tried to add my file here but the attchments button wont work.


    Think i need more rights to share documents
    Last edited by Lstrike; 11-23-2018 at 05:33 PM.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    The attachments icon does not work and has not worked for quite some time. Try instead...

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    HTH
    Regards, Jeff

  5. #5
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    in the first sheet i have a table with week numbers on top. and task lists on the right. and i want the cell that crosses these points to change color eksample week 1 with task 1. the conditions for this is as follows
    my next sheet is the register sheet where name and task numbers and hour is created
    like this:
    [week 1][task 1]
    [week 1][task 3]
    this is genrated by a mcro to always add next registration on top.

    this should activate a conditional formating to change a cell in in first sheet to change color. if there has been a registation that meet one of my weeks and one of my tasks

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    Please attach your sample sheet here.

  7. #7
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    document test
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    so if you see from the last sheet that is been generated by my 2 sheet. that the last registration sould turn the cell T 23 into a diffent color. even if there is a new registration.

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    Quote Originally Posted by Lstrike View Post
    that the last registration sould turn the cell T 23 into a diffent color. even if there is a new registration.
    Is this row 6, Task 3 because it is a duplicate of row 5? I probably need a little more explanation if that is not right.

  10. #10
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    there will be duplicate registration.
    because there will be a calculation for the houers spent on this projct to. that is a whole different case but this i have selution for.
    the only think i want is some way to detect to values that i need and then turn a cell green to indicate that there has been a registration in that week for that spesific task

    Every registration is done by drop down menus on sheet 2. And is generated on the top on sheet 3.
    so the last registration that was done was:
    47 23.11.2018 Stephen Task 18 1

    this shold indicate that the cell T23 in the sheet 1 to change color.

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    I'm sorry, maybe I'm still hung over from the Thanksgiving meal yesterday, but this still does not resonate with me.

    So on Sheet 1 (Prosjektplan), you want cell T23 to change to a color because Sheet 3 (timeliste) in Row #2 is the last registration.

    How do you determine it's cell T23 that needs the color. I can understand column a has 47 and the task is 18, but the name for task 18 on Sheet 1 is Ronnie, not Stephen?

    Are you just matching on 47 and 18? Your profile says you are using Excel 2007. Is this correct?

    Conditional Formatting
    • Highlight applicable range >> =$S$6:$AR$34
    • Home Tab >> Styles >> Conditional Formatting >> New Rule
    • Select a Rule Type: Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =AND($C6=timeliste!$F$2,S$4=timeliste!$A$2)
    • Format… [Number, Font, Border, Fill]
    • OK >> OK
    Last edited by jeffreybrown; 11-23-2018 at 07:30 PM.

  12. #12
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    transalted some more of the text to make it more easy to understand.

    name can be ignored only states who is resposible to the task not who is working on it.

    and added som hour calculation to the sheet..
    i ony want to color the cells as stated before.. i will test your forumla in a sec
    probobly my bad english that make it hard for me to explain what i try to do.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    Hello again. tried your formula but it said it was an error. ind i cant say what is wrong since i dont know what everyting mean.
    I tried to change And to "Og" that is the norwegian transaltion. still error.

  14. #14
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    i have made an example without formulas to make sure everything i clear.
    There is 5 registrations in sheet 3. That should create 5 gray cells in sheet 1. that i have marked manual.
    Attached Files Attached Files

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

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    purple =AND(INDEX(weeknumbers,1,COLUMN(S6)-18)=lastWeek,ROW(S6)=MATCH(LastTask,$C$6:$C$34,0)+5)
    blue =AND(COUNT($E6:$F6)=2,S$4>=WEEKNUM($E6),S$4<=WEEKNUM($F6))

    LastTask ="Task "&MAX(VALUE((REPLACE(INDEX(TimeListe,,6),1,5,""))))
    lastWeek =INDEX(TimeListe,ROWS(TimeListe),1)
    TimeListe =timeliste!$A$2:INDEX(timeliste!$1:$1048576,COUNTA(timeliste!$A:$A)+1,7)
    weeknumbers =Prosjektplan!$S$4:$AR$4
    Attached Files Attached Files
    Ben Van Johnson

  16. #16
    Registered User
    Join Date
    11-23-2018
    Location
    Norway
    MS-Off Ver
    2007
    Posts
    10

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    thank you guys. the last one did not work after ii added more data in my 3 sheet.
    not sure what the problem was.
    I worked my way around it using sumifs and formated the cell to not show values only colors.
    but its good to know that there is a easy way to solve this. this was my final document regarding this problem.

    thanks for your help

  17. #17
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Color a cell if it meets 2 to assigned values in one other sheet.

    Pl see attached file.
    Formula for CF for cell S6

    =ISNUMBER(MATCH(S$4&$C6,timeliste!$A$2:$A$10&timeliste!$F$2:$F$10,0))

    Select S6:AR33
    Apply formula as new rule.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 11-24-2018 at 03:41 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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] How to change cell color from VBA assigned color
    By OpieWinston in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2017, 10:31 AM
  2. [SOLVED] Dynamically color cell on Summary sheet based on column values on data sheet
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-08-2016, 12:59 PM
  3. Fill Color if Cell meets condition of another cell
    By RobIzq in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2015, 04:12 PM
  4. [SOLVED] Values assigned to A3 Sheet 2 applied to any cell in sheet one when A3 is true
    By Brosoil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2015, 03:35 PM
  5. search cell values based on list of values in other sheet and add color to row
    By darkbraids in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2012, 08:35 AM
  6. Replies: 3
    Last Post: 03-23-2010, 06:50 PM
  7. [SOLVED] Color a cell and a value is automatically assigned to that color.
    By Bossi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2005, 01:06 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