Conditional Formatting to color cell using color of other cell

**ahlecks** · 06-26-2010, 04:02 AM

Hello,

I have read almost all subject relating to conditional formatting but sad to say I have not found a solution to what I need for my excel file to work.

I need to use conditional formatting in determining the color of another cell and apply the same color with the current cell. I am using only 2 colors, red and green.

Help please, Thank you.

**teylyn** · 06-26-2010, 06:14 AM

Hello ahlecks, welcome to the forum.

Looking at your conditional formatting formula in B8, I see

=IF(ISBLANK(B10)=TRUE,B8<$J$3,)

what do you want to achieve? ISBLANK() delivers a Boolean True or False, so a comparison with "TRUE" is not required.
The ISBLANK function references B10. Why?

If B10 is blank, then you compare B8 to J3. If B10 is not blank, then you have a False as the result. Is that really what you want?

If so, the CF for B8 could be more efficient and less confusing with

=and(isblank(b10),b8<$j$3)

Now, can you specify which other cell you want to format based on which other cell(s)?
As a general principle, if you have a cell with a CF, then you can use exactly the same condition to color another cell to the same format.

Some more explanation and a mocked up example of the expected result will be beneficial to helping you find a solution.

cheers,

**ahlecks** · 06-26-2010, 07:59 AM

Thanks for your response teylyn,

my apologies for the confusion. i have attached a revised file which contains more information than the previous. the columns that are colored blue are the task which needs to be done with in the date shown on the "Planned" row for each plan. (disregard the target row). when we have finished a particular task, we put in date in "Actual" row and the difference will be shown in the "Variance" row automatically. If you could check, i hope i did it right.

There are certain task that are not done in time so the cell on the "Planned" row will be highlighted as red, as critical. this i compared on the date of the file in which i used the now(), current date. So everytime, there is a task that is behind the current date it will mark red. Everytime we enter a date on the "Actual" row for a certain task, it will automatically be highlighted as green, meaning work done same color applied to the date above it on the "Planned" row, even if it is beyond the current date. Now, there are plans that will not follow the normal procedure, example; from Approval from SM it would proceed directly to implementation without passing through Issuance... and Response..., with that i need to apply green color to all task as it already done on the last one...

hope i get this cleared.

I really appreciate your help. Thank you.

**teylyn** · 06-26-2010, 09:38 AM

hope i get this cleared.

Not really.

This is getting more complicated rather than easier.
cell C8 has three conditions, i.e.

=E8.Interior.ColorIndex=4 will be green (but it is not a valid syntax, so will result in an error, anyway)
=IF(ISBLANK(C10)=TRUE, C8<$K$3,) will be red (I still hold that this can be expressed more efficiently as shown above)
=C10>0 will be green

But the corresponding cells below have only two conditions
=IF(ISBLANK(C14)=TRUE,C12<$K$3,) will be red
=C14>0 will be green

I don't see the logic. Instead of uploading a worksheet that does not do what you want, mock up some data with "before" and "after", showing what you actually want to happen. The emphasis here is on "showing". Demonstrate what you want to see where. Give an example of what the data is before the manipulation, then give an example of what you would like to see after something has happened, like date changes, data entered, moon completed a cycle, or whatever.

I realise that you can't do that with conditional formatting, but use hard coded formatting and explain why the colors are there.]

especially this bit

Now, there are plans that will not follow the normal procedure, example; from Approval from SM it would proceed directly to implementation without passing through Issuance... and Response..., with that i need to apply green color to all task as it already done on the last one...

is what I'm not getting.

It's late, I'm tired and will be offline soon. Maybe someone else will take over. If not, you may want to do some more explaining.

**ahlecks** · 06-27-2010, 01:04 AM

yes, C8 has three conditions:

1.) should be green; when E8 is green (regardless if "Actual" row contains a date or not)
2.) should be red; when E8 is not green; when C8 has already lapsed; and when there is no date on "Actual" row)
3.) should be green; when C10 contains a date

the first four dates on the "Planned" row should have the same conditions settings.

file attached contains two worksheets labeled before and after. before contains all planned dates where no data entry has been done yet. on after, dates are entered on the "Actual" row. When you entered a date on the "Actual" row, its corresponding "Planned" cell will be highlighted as green meaning the task is done.

there are some instances that plans are implemented without passing through the usual procedures like in plan A where it was not issued and have no response. On the attached file you will notice on the after worksheet specifically Plan A that although it has already been implemented (column K), column G & I is still red. In this case, where a particular task that has been bypassed it should be colored green even when they do not contain dates on their "Actual" row.

**teylyn** · 06-27-2010, 02:12 AM

I don't see how this is working:

=E8.Interior.ColorIndex=4

It delivers an error for me, regardless of the color if cell E8. It is not valid syntax for conditional formatting. It is VBA syntax, and does not work in a cell. What do you get?

If you want to use CF on a cell, depending on the CF of other cells, you can not query the color of the cell. CF does not change the Interior color index of a cell.

So, what you need to do is to check if the individual conditions of each cell are true and then use the output to color your cell.

For example, you have CF in A1, B1, C1, each with its individual formula. For the sake of the exercise lets call it conditionA, conditionB, conditionC.

Now, if you want to color D1, you need to use a formula along the lines of

=and(conditionA, conditionB, conditionC)

If all three conditions A, B and C evaluate to TRUE, then D1 will apply the CF you set.

cheers,