I need to conditionally format a cell based on the outcome of a formula and the value in another cell and I’m stuck as to how to do it.
I have a table that has the next 50 weeks of our project across the top row (row 10) and subsequent "week ending" date in the next row (row 11) and two columns (columns I and J) that I enter the start and end dates in respectively (always a 5 day working week whose end date corresponds to that stated in row 11).
For each row I have used conditional formatting based on the function (=AND(L$11>=$I14,L$11<=$J14)=TRUE) to highlight the cell on row 14 for which the dates I enter (I14 and J14) fall within the given week of the project based on the ending date of each week (row 11).
This has worked perfectly as a scheduling tool up till now however I have been asked to change it. I now require that the formatted colour be one colour when the value in a separate column (column F) is 1 and another colour when the value in the column F is 2. Any help would be greatly appreciated.
I have attached the document to help if needed and please post if it's not able to be done please.
Thanks in advance...
I have a table that has the next 50 weeks of our project across the top row (row 10) and subsequent "week ending" date in the next row (row 11) and two columns (columns I and J) that I enter the start and end dates in respectively (always a 5 day working week whose end date corresponds to that stated in row 11).
For each row I have used conditional formatting based on the function (=AND(L$11>=$I14,L$11<=$J14)=TRUE) to highlight the cell on row 14 for which the dates I enter (I14 and J14) fall within the given week of the project based on the ending date of each week (row 11).
This has worked perfectly as a scheduling tool up till now however I have been asked to change it. I now require that the formatted colour be one colour when the value in a separate column (column F) is 1 and another colour when the value in the column F is 2. Any help would be greatly appreciated.
I have attached the document to help if needed and please post if it's not able to be done please.
Thanks in advance...
Attachments
-
43 KB Views: 654