Conditional Format Based on 2 column range in another sheet

[Xirom431]

Hi,

I have the table in Sheet 2. The week 1-4 Target values are dynamic based on number of days in a month in Sheet 1 Completed Column B. Everyday, people enter the Completed value. The Target values are in column C and dynamically change each month. Table in Sheet 1 is one year calendar. Table in Sheet 2 displays TODAY() value in Column B of table in Sheet 1. How do I apply conditional format to Actual value in Table in Sheet 2 if the TODAY() Actual value is less than TODAY() Target Value? Thanks in advance for the help

Table in Sheet 2

Table in Sheet 1 Date is in Column A. Completed is in Column B and Target is in Column C.

 

[arthurbr]

Have you used merged cells for the table in sheet2?

 

[Xirom431]

Have you used merged cells for the table in sheet2?

Yes.

 

[Peter_SSs]

Is this what you mean?

Xirom431.xlsm
	A	B	C	D	E
1	Target	25	50	75	96
2	Actual	74
Sheet2

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(TODAY(),Sheet1!A:B,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E2	Expression	=B2<VLOOKUP(TODAY(),Sheet1!$A:$C,3,0)	text	NO