Autofill a Cell on Sheet 2 with a Value from a Colored Cell on Sheet 1- Is this Possible?

[beachbum07]

I want to auto populate a call with the value of a different cell that has been colored on a different page.

Example:

1.) On Sheet 1, Cells range A1-A10 have values to choose from
2.) I choose A7 and change the cell color to gray
3.) It auto populates Cell B1 on Sheet 2 with the value of the gray colored cell I selected on Sheet 1

Is this possible?

[shg]

Changing the formatting of a cell does not trigger an event that would allow that to happen automatically. Color is not data to Excel.

[beachbum07]

I saw a forum before where a guy figured it out but can't find it again.

[beachbum07]

Just brain storming...

is there a away to search for a colored cell in a cell range and auto populate another cell with the value of the found colored cell?

[shg]

Yes, but you'd need to trigger the macro manually, or invoke it in the Calculate event, or the Change event, or the Sheet2 activate event (which would have some warts).

[beachbum07]

If you can do conditional formatting to change the color of a cell based on the value, why can't you auto populate a cell value based on the color of another cell?

What are warts?

[shg]

Changing the contents of a cell triggers an event. Formatting a cell does not.

If you had a single window open, colored a cell, and then switched to sheet2, the sheet2 activate event could do (something) to find the colored cell on sheet1, ..., which would make it appear it had happened automatically.

If you had two windows open, then formatting a cell on sheet1 would cause no change of sheet2 until you activated the other window.

Cell content is data to Excel; cell formatting is aesthetics.