+ Reply to Thread
Results 1 to 5 of 5

IF color THEN value

  1. #1
    Registered User
    Join Date
    05-27-2021
    Location
    Sweden
    MS-Off Ver
    10
    Posts
    4

    IF color THEN value

    Hi,

    I'm new to this and just can't seem to work out how to do this. I am trying to write a function that searches through G7 to G17 in Sheet2, and if it finds a certain color (13421823) in a cell, for example G10, then it automatically puts the value of A10 (an employee) in a different cell (for example B4) in a different sheet (Sheet3).

    Thank you so much in advance!!
    Attached Files Attached Files
    Last edited by lelly1337; 05-27-2021 at 04:18 PM.

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: IF color THEN value

    Here's a macro that can be executed via a button on the sheet. But note that if there are multiple cells with that color only the last referenced name will be copied to B4 on sheet3. Do you want the code to do something different?

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-27-2021
    Location
    Sweden
    MS-Off Ver
    10
    Posts
    4

    Re: IF color THEN value

    Thank you so much for your prompt reply! But yes, I'm afraid I explained it rather poorly.

    If you look at sheet 1 in the above file, which is used for scheduling, you can see there are employee names in the A column. Employees are on call when they are marked with the pink/redish color. I would like to have a macro on another sheet which states which worker is on call for a specific week (defined by who is on call on the first day of the week). For example, day 1 of week 1 "Name 3" in department 1 would be on call, thus I would like a macro that in B4 on the second sheet states "Name 3". On day 1 of week 3, the call is on "Name 6", thus I would like to B8 in the second sheet to say "Name 6" because of the color in the schedule.

    If possible, the best thing would be if I could find a macro that searches through both departments (i.e. for example B7:B17 AND B20:28) but only places the first match in B4 on the second sheet, and the second match in C4.

  4. #4
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: IF color THEN value

    Here you go. I tried to compute all duty cycles for you, but the data for duty 3 is not consistant.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: IF color THEN value

    Before progressing with a more complex VBA approach, would alternatively suggest to simplify the model structure and keep formula based.

    For B7:AT17, instead of hard coding 1s + manual colour formatting, inputs could be tied to unique numbers for each task/duty (e.g. 1, 2, 3) + conditional formatting. For subtotal rows 30-32 use COUNTIF, row 34 use COUNT. If output sheet is tied to a specific day/column, simple INDEX MATCH would do.

+ 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. Change cell format (cell color+font color) based on color of another cell
    By Dedaluss in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2017, 03:27 AM
  2. Replies: 3
    Last Post: 05-30-2014, 10:24 AM
  3. Replies: 4
    Last Post: 12-29-2013, 11:41 PM
  4. Mouse rolls over to cell will change the tab color, font color and tp bold type
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-20-2013, 10:36 AM
  5. Replies: 0
    Last Post: 09-26-2012, 01:08 PM
  6. Replies: 12
    Last Post: 09-18-2012, 03:13 PM
  7. Replies: 0
    Last Post: 02-16-2006, 12:00 AM

Tags for this Thread

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