Conditional Formating Multiple Variable

[treaves04]

I am trying to use conditional formatting to highlight cells that are partial rent payments. It would be preferable that it not highlight months that are no due yet. I can do individual cells using =AND("Rent Collected"<"Monthly","Month"<"Today","Month">"Previous Month") or as it would be seen on the spreadsheet below <d6,i1<d87,i1>=AND(I6<D6,I1<D87,I1>H1) where d87 is simply =TODAY(). I cannot apply this to the entire work sheet however as the formula does not float to the correct cells. With almost 900 cells to apply the formula too manual entry is not a good option. Any help is appreciated. Thank you.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Number	Street	Code	Monthly
Clinch Dr	106CD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]January-18[/TD]
[TD="align: right"]February-18[/TD]
[TD="align: right"]March-18[/TD]
[TD="align: right"]April-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]June-18[/TD]
[TD="align: right"]July-18[/TD]
[TD="align: right"]August-18[/TD]
[TD="align: right"]September-18[/TD]
[TD="align: right"]October-18[/TD]
[TD="align: right"]November-18[/TD]
[TD="align: right"]December-18[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]106[/TD]

[TD="align: right"] $ 850.00 [/TD]
[TD="align: right"] $ 850.00 [/TD]
[TD="align: right"] $ 850.00 [/TD]
[TD="align: right"] $ 850.00 [/TD]
[TD="align: right"] $ 850.00 [/TD]
[TD="align: right"] $ 700.00 [/TD]
[TD="align: right"] $ 850.00 [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 800.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]

</tbody>

Rent Rolls​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</d6,i1<d87,i1>

 

[Worf]

The CF formula could look like this:

 

[Peter_SSs]

See if this is what you want.
Remove any existing Conditional Formatting from the range.
Select from E2 to the bottom right cell of the rent payments range and apply the CF formula shown. Be careful to include the $ signs in the correct places.

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Monthly	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
2	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	900.00	1,000.00
3	500.00	500.00	200.00	150.00	500.00	120.00	499.00		500.00	500.00
4	850.00	850.00	850.00	850.00	850.00	700.00	850.00	800.00		800.00
82
84
85
86
87	7/10/2018
Rent Payments

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	1. / Formula is =AND(E2<$D2,E$1<$D$87)	Abc