Is this possible?

[finaddict]

Say you have sheet1 and sheet2. Some cells on sheet1 are shaded. On sheet2 you want, for example, cell A1 to list the cell numbers in the color the cells are shaded but without the cell letter. Just the number of the cell with the number being the color of the shaded cell. Sounds like VB coding to me and I'm not a VB guy yet. Would like to be though.

Thanks,
Steve

[Cbrine]

Public Function RColor(C As Range)
On Error GoTo Errorhandler

If C.Count > 1 Then Err.Number = 9999

RColor = C.Interior.ColorIndex
Exit Function


Errorhandler:
RColor = Err.Number
End Function

Paste the code to a module and us it as a formula in Sheet2 A1
=RColor(Sheet1!A1)
Then just fill down.

[finaddict]

I don't quite understand what I'm to do with the information you gave me. I'm new to this coding thing. Could you give a little more detailed instructions?

Thanks,
Steve

[Cbrine]

Steve,
From Excel press ALT-F11, this will open the VBA Editor.

Select View...Project Explorer

On the project explorer Right click on the
Microsoft Excel Objects Folder for the current sheet.
Select Insert...Module

Just paste the code to this new module and close the explorer(Don't worry the code is save with the workbook).

Now just type the function into the cells, like any other excel formula.

Have fun.

[finaddict]

CBrine....I couldn't get it to work but I'm sure it's my fault. But I have come up with an easier task. Let's say row1 on sheet1 has some shaded cells with a number in them. Then on sheet2 cell A1 would list all the numbers and change the color of the numbers to the color of the cell. Can that be done easier.

Thanks for being patient,
Steve

[finaddict]

I did like you told me but all I get in the cell is -4142.

Thanks,
Steve

[Cbrine]

Steve,
I think I may have misunderstood what you are looking for. The VBA code I gave you will return the colorindex number to another cell based on the source cell's background color. So if the background is white you get the number -4142.
Can you tell me what exactly you need?

Sheet1!A1= 100 Blue backgound

The formula should return to Sheet2!A1

100 and change the color of the cell to blue?

Is it the interior color or the font color?

If you want the formula to change the color of the cell, that's not possible with a function. A function can only return values to the cell. You might need to work with the WorkSheet_Change event to get the result you are looking for.

Let me know?

[finaddict]

I'll try to give a better example of what I'm trying to do. I'm not real good at explaining things but here it goes.

What I've got is a sheet (sheet1) that has selected cells which are shaded. They also have a number in those shaded cells. What I would like to do is on sheet2, starting in cell A1, list the numbers that is in the cell and each number be the same color of the cell.

For example:

Sheet1, column A has cells A44..A46 shaded black with a number in the cells. Then column B has cells B30...B42 shaded red with a number in the cells. This goes on across an entire worksheet. On sheet2 in cell A1, I would want it to read "##,##,##" in black. The "##" are the numbers in the cells. Then in cell A2 it would list the numbers in column B and the numbers would be red which is the same color of the shaded cells. In some columns there would be cells of more than one or two colors. For the rest of the sheet column C would list in A3 and so on. I hope this kinda makes some since. I need some pictures. That would probably help.

Thanks,
Steve

[Cbrine]

Steve,
I'm pretty sure I understand what you are looking for now. I don't think what you want is possible using VBA. I did a couple of quick tests and found, that while you can edit a cell and modify individual elements color attributes, there is no way to do that using VBA. When a cell has multiple colors, the VBA colorindex property gets set to null.

Sorry Steve.

If anyone else know how to use VBA to change a color within a cell, let us know.
ex.( 12 13 14)

[finaddict]

CBrine,
The example you posted is what I'm looking for. For instance 12,13,14 would be listed in cell A1 sheet2 because sheet1, column A, had cells that were shaded red, dark blue, blue with the numbers "12, 13, 14" in them and then sheet2, cell A2 would list what was in sheet1 column B and so on. I hope someone can figure out a way to do this because it would save a ton of time in typing, which I'm not very good at in the first place.

Thanks for your time CBrine,

Steve