+ Reply to Thread
Results 1 to 6 of 6

Trouble using Custom function in an array to determine Cell Colour

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Trouble using Custom function in an array to determine Cell Colour

    Hi everyone, I'm new on this forum, have searched for an answer to this problem for a while with no success.

    Basically I am trying to design a spreadsheet for work with limited knowledge of VBA. On a particular sheet there are certain rows shaded in grey (colour index = 15). I am using some data from this sheet (sheet1) on another sheet (sheet2) and need to do this using arrays as data on sheet1 is frequently reordered / deleted / inserted (entire rows) and this breaks my system as excel updates the links on sheet 2 and so reordering does not occur. Also I need to advantage of arrays updating real-time.

    What I am trying to achieve is getting the rows shaded grey on sheet1 to also become shaded grey on sheet2. I can do this using conditional formatting. Currently my workaround is to insert a marker such as "." in a hidden column in the rows that have gray shading. This is then read by an array on sheet2 and conditional formatting applied. This works perfectly, but I am wary that if a new grey row is created, or someone doesn't copy the "." or accidentally deletes it, then the system will break (yes, I am paranoid). My ideal situation is therefore to read the cell colour of the cells.

    I've written a function to do this, which works:

    Please Login or Register  to view this content.
    This works perfectly when used in excel on sheet2, reading off the corresponding cells in sheet1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Sheet2 is protected and read only so I'm not worried about the hidden column in which this is being deleted / messed with.

    The problem is when I try to use this formula in an array over e.g. Sheet2 F1:F199 it returns a #value. And I just can't get it to work

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    I have created a workaround using VBA and putting this code on Sheet2:

    Please Login or Register  to view this content.

    This does exactly what I want but I need to use VBA to make any changes, which may be difficult for someone else in the future who doesn't understand VBA, and this requires me to change to Sheet2 inorder to update the values. The only potential problem with this is that someone may print off the workbook (including sheet2) without changing to it after having made changes to sheet1. And therefore things may mess up somewhat. Hence I would prefer to use the array method using the function.

    Any help would be much appreciated! Thanks

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Trouble using Custom function in an array to determine Cell Colour

    perhaps
    Please Login or Register  to view this content.
    called using
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    09-21-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Trouble using Custom function in an array to determine Cell Colour

    Wow! Thanks JP, for the quick and excellent response!

    That actually makes it so much easier and does exactly what I want it to

    Now I will spend the next hour or so figuring out why it works, but that's half the fun!

    Thanks again

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Trouble using Custom function in an array to determine Cell Colour

    you're welcome :-)

  5. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Trouble using Custom function in an array to determine Cell Colour

    @ karanm83

    Welcome to the forum.

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  6. #6
    Registered User
    Join Date
    09-21-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Trouble using Custom function in an array to determine Cell Colour

    Thank you Cutter and my apologies for forgetting to mark as SOLVED
    And thanks for the pointer, have done the reputation thing now too

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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