Results 1 to 12 of 12

20071018, 18:43 #1
 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.

20071018, 18:56 #2
 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 Userdefined 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

20071018, 19:10 #3
 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

20071018, 19:43 #4
 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?

20071018, 19:44 #5
 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

20071018, 19:52 #6
 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

20071018, 20:18 #7
 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??

20071018, 21:09 #8
 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???

20071018, 21:10 #9
 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 relook at my post b/c I have now attached a sample file that might make it clear...thanks.

20071018, 21:11 #10
 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 relook at my post b/c I have now attached a sample file that might make it clear...thanks.

20071018, 21:21 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20071018, 21:50 #12
 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