+ Reply to Thread
Results 1 to 7 of 7

User defined function to copy the cell color to another cell (actual painting the color)

  1. #1
    Registered User
    Join Date
    10-15-2017
    Location
    USa
    MS-Off Ver
    2013
    Posts
    2

    User defined function to copy the cell color to another cell (actual painting the color)

    User defined function to copy the cell color to another cell (actual painting the color).

    I am looking to write a code for copying the color of one cell to another.

    For eg iif I selected sell A1 and enter = copyColor(D1).
    Want A1 to mirror the same color as D1.

    This code does it, but its a macro

    Please Login or Register  to view this content.
    I want to have a User defined funciton so that I can handover to my users ( who cant dot VBA) and use it in any of the cells they want.

    Please help!
    Last edited by jeffreybrown; 10-16-2017 at 07:37 AM. Reason: Please use code tags!

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: User defined function to copy the cell color to another cell (actual painting the colo

    This isn't a UDF but it will fill any cell that is clicked on the sheet with the color of D1
    Please Login or Register  to view this content.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    10-15-2017
    Location
    USa
    MS-Off Ver
    2013
    Posts
    2

    Re: User defined function to copy the cell color to another cell (actual painting the colo

    Thats not what I am looking for.

    I dont know what cells are to be colored with target value, so I want to create a formula where user can say =copyColor(D1). So color of D1 gets copied to the cell where that formula is entered.

    Please Login or Register  to view this content.
    The above function gives the decimal value of the color (Red = 255), but I actually want the cell (where you enter the formula) to be painted red.
    Last edited by jeffreybrown; 10-16-2017 at 07:39 AM. Reason: Please use code tags!

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: User defined function to copy the cell color to another cell (actual painting the colo

    After researching a bit, I have found that you cannot use a function to change the state of a cell in excel. Take a look at these links, they might be of some help to you.
    https://stackoverflow.com/questions/...he-cells-color
    http://www.cpearson.com/excel/Colors.aspx

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: User defined function to copy the cell color to another cell (actual painting the colo

    Isn't this a mild version of sledgehammer and nut?

    How difficult is to train users to use the Format Painter functionality? Surely that's simpler than getting them to use a non standard function and type stuff in a cell. Certainly there's far fewer key strokes.
    Last edited by Richard Buttrey; 10-16-2017 at 07:46 AM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: User defined function to copy the cell color to another cell (actual painting the colo

    You could do something like this.

    Put this UDF in a normal module. Note that I added an optional ValueToShow argument, for what you want the cell to say.
    Please Login or Register  to view this content.
    Then put this code in the ThisWorkbook code module
    Please Login or Register  to view this content.
    If you put =CopyColor(A1, "cat") in a cell, the cell will have the color of A1 and show "cat".

    Note that changing a cell's color will not trigger calculation. If the user changes the color of A1, then one must force calculation to get it reflected in the cells with the formula. (Volitile functions are not triggered by a cell's color change only by changing a value)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  7. #7
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: User defined function to copy the cell color to another cell (actual painting the colo

    Hi sumitgangwani,

    The following macro (see the attached working file) may do what you want. It is NOT a UDF (User Defined Function) as you requested.

    Ctrl B is shortcut key to Macro CopyColorToSelection(). The Shortcut key can be changed to Ctrl plus any letter.

    To run the Macro:
    a. Select the Destination cell(s)
    b. Run the Macro by using shortcut key 'Ctrl b', or by pressing 'Alt F8', or by pressing the CommandButton.
    c. Select the source color cell when prompted.

    In an ordinary code module:
    Please Login or Register  to view this content.
    Lewis

+ 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. [SOLVED] Need to change cell color based off of RGB Color being defined in 3 cells
    By Chris McGlothen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-01-2017, 02:13 PM
  3. User defined function- count color and count only visible rows
    By marsjanik1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2016, 03:05 PM
  4. Replies: 4
    Last Post: 12-29-2013, 11:41 PM
  5. Replies: 2
    Last Post: 02-25-2013, 03:36 AM
  6. Color a result cell in a user defined function
    By aaa in forum Excel General
    Replies: 1
    Last Post: 05-08-2006, 11:25 AM
  7. [SOLVED] Using boolean function for color fill:get color cell
    By Kanchi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 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