Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Using cell color to input data (Excel 2003)

    Hi all....I have some cells in range B1:B275 that are colored; some are not colored. I am looking for a formula to put in range C1:C275 that will look at the cells in B1:B275, and if a cell in that range (column [img]/forums/images/smilies/cool.gif[/img] is colored, the formula will automatically put an X in the adjacent cell in column C (eg: if B34 is colored, then C34 shows an X; if B40 is not colored, then C40 shows no X)......any suggestions? Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using cell color to input data (Excel 2003)

    Check out Chip Pearsons Functions For Cell Colors. If you add these User-defined funtions to your workbook, you can use them in the formulas in column C to check out the colorindex in Column B If the color index = xlNone (=-4142) then the cell has no explicit color set.

    eg in C34 something like this (once the function is added into a module)
    =If(CELLCOLORINDEX(B34) = -4142,"","X")

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using cell color to input data (Excel 2003)

    Hmmm...thank you sdckapr, but your suggestion is too complex for my level of Excel....I looked at the link you suggested, but I can't understand what I am supposed to do.....I was hoping for a formula in column C that would 'see' if the cells in col B are colored..and if so, then an X would appear in the adjacent cell in column C

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using cell color to input data (Excel 2003)

    ...ps: and if I wanted to add those functions to the workbook (if that's the only way to do it) how do i add them?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using cell color to input data (Excel 2003)

    That is what Steve's post told you how to do. There is no way for a formula in Excel to see if a cell is colored. Therefore, you must add the UDF (User Defined Function frp, from Chip Pearson's site to a module in the workbook and use that function in the formula in the cell. If you can't figure that out, then make another post explaining what part you don't understand. Just saying that it is too complex does not tell anyone what needs to be explained.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using cell color to input data (Excel 2003)

    Open the workbook and then press Ctrl+F11 (That's function key 11). This should put you into the VBA editor. From the Insert menu select Module. That should give you an empty module named Module1 in the edit window (there may be one line at the top of the window that says Option Explicit, depending on how the options are set in your Excel. Copy the UDF code and paste it into the module window. Close the VBE by clicking on the X in the right corner of the title bar. You should now be able to use the function in Steve's post in the worksheet.

    If you want to use the formula in any workbook on your machine, put the UDF into your Personal.xls file. See my star post <post:=118,382>post 118,382</post:>.
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using cell color to input data (Excel 2003)

    ..ok...I think that I am understanding this, but whihc UDF codes from Chip Pearson's article do I copy and paste into that module? and I assume I have to modify it in some way to make is work over the range that I want??

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using cell color to input data (Excel 2003)

    ...ok gentlemen...I hope that this will explain what I am trying to do...I have attached a sample file....does this help? Sorry to be so dense about this, but maybe the sample will make it clear???
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using cell color to input data (Excel 2003)

    Hi Legare....can you re-look at my post b/c I have now attached a sample file that might make it clear...thanks.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using cell color to input data (Excel 2003)

    Hi Steve....can you re-look at my post b/c I have now attached a sample file that might make it clear...thanks.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using cell color to input data (Excel 2003)

    Why don't you do it the other way round? You can use Conditional Formatting to color a cell if there is an X (or whatever) in it. No VBA code needed at all. See attached version.
    Attached Files Attached Files

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using cell color to input data (Excel 2003)

    Here is a copy with Pearson's functions. Change the color of the cells in Col C and press <F9> to calculate and the Xs will appear in the colored cells. It does not use Col D Periods but directly reads the colored cells in Col C.

    BUT, I think Hans has a better solution. It is easier to put an X and use conditional formatting in the cell than to color the cells manually. It also updates automatically (not requiring you to recalc...)


    Steve
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •