Solved How to change a cell's color to match another cell's color

August 7, 2015 at 23:53:03
Specs: Windows 8
Hi!

I am using Excel to create a rooms inventory. There are over 2000 rooms. I have to keep a record of which rooms I have completed photographing.

In one tab, there is a list of all the rooms I have done. I have organized it so that there are 45 columns, one for each floor. There are varying rows as each floor has different number of rooms. Originally, they were ordered from lowest to highest. For example, the first column is the third floor. The first value in the column is room 301, the last room is 384. Each day that I complete a room, I turn the cell green and then I sort the columns individually so that the "green" cells are moved to the bottom of the column and the unfilled cells - the not-yet complete rooms remain at the top.

I tried researching this - tried conditional formatting and now hope there's a code for this

Here is what I want to do.
I have another sheet, with the information displayed differently - more like a database of information about the rooms instead of a tracker like I had before. Each row has data about the room. For example, the room 301 has column headings such as "floor, type, description, etc. The last column/item for each for row/room is it's status - whether it is completed or not.

Is there a way to make that cell in the complete column correspond to the room number in the first worksheet I described before (where it was just columns of rooms)

I manually change the color the the first worksheet - the tracker sheet - and I would like to know if it is possible to have that manual change automatically trigger a change in whatever corresponding room for the completed status cell.

My main concern is that I rearrange the data in the tracker sheet every day - since I sort the color cells to the bottom when it is complete.

Any advice? If this is not a possible task, I'd like to know to! Then I'll just fill out the database worksheet manually as well.


See More: How to change a cells color to match another cells color

Report •


#1
August 8, 2015 at 16:12:51
✔ Best Answer
Checking for a cell color is not easy in Excel, as there is no built in function.

While using VBA does offer a possible solution, my VBA skills are just above nil.

Additionally with Conditional Formatting, if you are using two sheets,
it will only work if you use Defined Names.

Each day that I complete a room, I turn the cell green and then I sort the columns individually so that the "green" cells are moved to the bottom of the column

One possible solution is to use a dedicated column for Job Completion,
it could be a Date cell or some other type, but that would at least give some type
of cross reference for the Conditional Formatting to possibly use.

Something along the lines of:

If sheet1 room number and ( sheet 2 room number and is completed )

Might work, the Sorting may be a problem.

MIKE

http://www.skeptic.com/


Report •

#2
August 10, 2015 at 09:00:35
re: "Additionally with Conditional Formatting, if you are using two sheets,
it will only work if you use Defined Names."

Just to be clear, this is only true if an Excel based solution is being sought. You would not need to be concerned with CF and Named Ranges if are using VBA. However, you would need to be concerned with the colors that are chosen within CF. VBA can determine (and use) most of the solid fill colors within CF, but once you start getting into shading and gradients, all bets are off.

I've been really busy lately and haven't had time to look into a VBA solution for this, but I'm sure one exists. The weeks before and after a vacation week get so hectic that it's almost not worth taking the vacation! ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question