Conditional formatting a cell based on values in two different cells

[srmp07]

Hi all,

Could someone please help me to resolve the below.

My attempt is to do conditional formatting for a cell based on values in two different cells. I have added some formula, but it does not seem to work.

Please see attached a sample spread sheet.

The below is what I am looking for (taking “B4” & “C4” as an example)

Conditional format cell "B4" to red colour & font to Black Colour,
If there is a text/value "UH" in “ROW 4” starting from D5, but only if the "dates" in "ROW 2" is "LESS THAN" or "EQUAL TO" Today's date. (ie: if there Is no "UH" in ROW 4 or if there is "UH" in ROW 4, but the date in ROW 2 is "GRATER THAN" TODAY's date, then cell "B4" remains clear without any colour)

Likewise Conditional format cell "C4" to red colour & font to Black Colour,
If there is a text/value "UH" in “ROW 4” starting from D5, but only if the "dates" in "ROW 2" is "GRATER THAN" TOMORROW’s date. (ie: if there Is no "UH" in ROW 4 or if there is "UH" in ROW 4, but the date in ROW 2 is "LESS THAN" TOMORROW,s date, then cell "B4" remains clear without any colour)

I am not sure if my above explanations make clear what I am looking for!

Just to explain my requirement for the above, the attached example is taken from an actual spreadsheet which has plenty more columns and many other text/value on that and the “UH” stands for pending payments. The payments are checked/made every two weeks. The actual sheet will be already updated with “UH” from the start to end (ie: from 1st April 2013 to 31st March 2014). However the payments are made based on the eligibility as per the date of payment and we end up in scrolling from D5 to ND5 every time to find out if it is to be paid now or it is for a future date. The above will enable us to identify and ignore the future days straightway rather than scrolling and finding out.

Look forward for your help
Thanks in advance.
Raj

[Richard Buttrey]

Hi,

Please manually add the cell results you expect to see along with any colours for a representative sample of cells/rows. Since 'Today's' date is important to this, assume a particular day and tell us what it is when you apply your colours

You mention row 5 but not the rest of the rows 6:10. Are they involved in any way. And what about 11:15, 16:20 etc..?

[srmp07]

Hi Richard,
Thank you for your reply.

I have now updated the sheet with “UH” typed in GF5,GG5 & GH5 only.

Here text typed in GG5 corresponding date in GG2 (cell above) match to today’s date (considering today’s date is 03.10.2013), and text typed in GF5 corresponding date in GF2 is less than today’s date & text typed in GG5 corresponding date in GG2 is equal to today’s date.

A. The "UH" values in GF5 /GG5 should change”B5” to red colour (ie: any “UH” values from D5 until GG5)
B. The “UH” value in GG5 should change “C6” to red colour (ie: any “UH” values from GH5 until ND5)

In my previous sample sheet Row 5 was taken as an example only. Yes, in fact the same applies to rows 6:10, 12:15, 17:20 7 21:24. However I have deleted the rest of the rows in the current attached sheet (Updated-Sample Spread Sheet for Conditional Formatting.xlsx) to avoid confusion. I if could get the above done I will copy the same formula to all the required rows in original sheet.

I have also attached another two spread sheets (sample1 & sample2)- manually added the cell result as per your advise.
Attachment "sample1" is for question A and attachment "sample2" is for question B

Thanks again and look forward for your help
Raj