Conditional Formatting Based on 2 cells

[partentus]

Hi,

I'm wondering if someone can help me?

I'm trying to write a formula to highlight the tender release date on a spreadsheet. I've got as far as being able to highlight a line where the cell states "Tender Release" (=OR($D$141="Tender Release") but I need to add to this a condition to only highlight the specific cell that matches the date in cell F141 to the date line FF8:HL8; can anyone help?

Thanks!

 

[Joe4]

Is this what you are looking for?

=AND(D141="Tender Release",COUNTIF(FF8:HL8,F141)>0)

 

[partentus]

Hi Joe4,

Thanks for this, it's almost what I'm looking for except I need the cell to be highlighted to be the specific date on the line, if that makes sense. What I'm trying to do is make a type of a Gantt chart so i have dates for tender release in columns F and then on the Row 141 I need to highlight the specific cell that corresponds to the date line at the top of the chart, in this case 05/01/18, but this will change depending on the tender, so I need the formula to find the date 05/01/18 in FF8:HL8 and highlight it on Line 141.

Thanks for your help

 

[Joe4]

I am not sure I understand. You just place the Conditional Formatting on the cell you want highlighted.
Are you trying to highlight F141, or the matching cell in FF8:HL8?

 

[partentus]

Sorry for not explaining myself very well...

What I'm hoping for is a formula, whereby I can look for the tender release date, find it on the date line and then highlight the cell under the date line on the Tender Release line. In this case the Tender Release date is 02/01/18 so I need to highlight cell J141. Is this anymore help?

[TABLE="width: 1684"]
<colgroup><col span="5"><col><col span="2"><col span="13"></colgroup><tbody>[TR]
[TD]Ref[/TD]
[TD]Contract[/TD]
[TD]Type[/TD]
[TD]Activities[/TD]
[TD]Notes[/TD]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Who[/TD]
[TD]Jan-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]03/01/2018[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD="align: right"]06/01/2018[/TD]
[TD="align: right"]07/01/2018[/TD]
[TD="align: right"]08/01/2018[/TD]
[TD="align: right"]09/01/2018[/TD]
[TD="align: right"]10/01/2018[/TD]
[TD="align: right"]11/01/2018[/TD]
[TD="align: right"]12/01/2018[/TD]
[TD="align: right"]13/01/2018[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]Tender Release[/TD]
[TD] [/TD]
[TD="align: right"]02/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] Highlight here[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD="align: right"]05/01/2018[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Highlight here [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD="align: right"]12/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD="align: right"]15/01/2018[/TD]
[TD="align: right"]-3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD="align: right"]15/01/2018[/TD]
[TD="align: right"]-3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXX[/TD]
[TD] [/TD]
[TD="align: right"]18/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

I see. So, for line 141, you would select cells FF141:HL141 and enter this Conditional Formatting formula (assuming the header dates are in row 8):

=AND($D141="Tender Release",$F141=FF$8)

To apply this to multiple rows at the same time, do the following:
- select the entire range you want to apply this to (column FF in whatever row your data starts in over to column HL down to the last row of data)
- update the row 141 references in the formula above to the first row that you selected for your data range (Excel will automatically adjust it for the other rows)
- be sure to leave the absolute references ($) in my formula where they are; do not add or remove any

This should do what you want.

 

[partentus]

Thank you so much for your help, I really appreciate it

 

[Joe4]

You are welcome!