Hello everybody,
Is it possible in Excel 2010 to relate 2 cells, each on another worksheet, with eachother, and if I change the color of one, the other automatically changes also?
It seems so simple, but the only way I can accomplish this is by copying the cell to the other worksheet and paste it as a linked picture.
Isn't there another way?
Thanks in advance
This is very difficult for two reasons:
1. Excel has no built-in function that will tell you the color of a cell (assuming you mean fill color)
2. Excel VBA cannot easily detect if the only thing about a cell that changes is its fill color
I think there are other ways to capture mouse clicks in VBA that would allow you to check for a color change, but to do so you would have to store the current colors someplace and then compare color after the click event to the color before.
I have seen similar questions but have never seen a satisfactory solution. With luck, someone will post one here. Actually, I like your linked picture solution.
What is causing the color change in the 1st cell? If you are doing it manually, then refer to Jeff's comments above.
If there is some logic to the change, then perhaps you could use Conditional Formatting, and use that same rule for the other cell
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for the replies!
FDibbins, the change of color is manually [days off have a different color as national holidays etc]
6StringJazzer, if you will use the sorting function [custom sorting (or adapted sorting)(I don't really know the English term of it)] of Excel, there is a possibility to sort on cellcolor. But I can imagine Excel can't easily detect the change of color.
The picture solution is one that takes a lot of time to produce, but as I see it till now, it's going to be the only one.
But I'm also looking to add some kind of value to the cells and link that to a color or a hatch. Or something else that would make the normal text or visual of the cells different.
Last edited by maheme; 07-22-2015 at 08:11 AM.
Yes, you can sort on cell fill color. But your question didn't mention anything about sorting, and I don't see how sorting by color can help you keep two cells on two sheets in sync.
6StringJazzer, sorry for the confusion. All I ment to say by refering to the sorting by cell color, is that there is some kind of identification per cell color.
Yes but there is no Excel function that you can use in a formula that tells you the fill color for a cell.
How about the option to sort columns by cell color?
So there has to be a value for each color that can be given to a cell.
If you look at scripting for a website for example, there are color given a name as '#aaaaaa' where the a's stand for an combination of letters and numbers.
In a few days, when I have more time, I'll look foor the possibility to add a script or something like that.
The thing is, color is formatting, along with font/size/bold etc, and these are all cosmetics that excel can apply so that WE can see things better. The formatting contains no data, and all excel formulas work on data.
Perhaps another option would be to use codes inthe cells and then have Conditional Formatting change the cell color based on those codes - then you could just base your changes elsewhere, on those codes?FDibbins, the change of color is manually [days off have a different color as national holidays etc]
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Post #2 still applies. VBA can determine what color a cell is. It can be expressed as a combination of red, green and blue. (The code used in HTML that you showed as #aaaaaa is also red, green, and blue. It has two hex digits for each. So, for example, #FF00FF has the maximum amount of red and blue, and no green. in VBA you could say RGB(255, 0, 255) for the same color.) But it can't be triggered to do something when the color changes. It just can't.Originally Posted by maheme
But Ford has the best idea, which is to use a code in a cell, like H for Holiday. Then use conditional formatting to color it, and make the font the same color, so you don't see the H. Then you can use VBA or maybe even formulas to make the other updates you need.
Thnx for the ideas! I will break my head on this soon ;-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks