conditional format formula or macro help

[danushka]

Dear friends my document column "P" & "Q" data like below.column "P" data all dates..

column "P"
9-Jul
9-Jul
8-Jul
5-Jul
9-Jul

column "Q"
9
25
9
10
25

My requirement is if column "P" yesterday date & column "Q" value more than 7 , then that column "Q" value should highlight in yellow.that yesterday date is not fix.it should be like (=TODAY()-1)..

pls give me conditional format formula or macro for this.thanks in advance..

 

[Peter_SSs]

Seems like you have just about given the formula yourself.

For my layout, select Q2:Qxx and apply the CF formula shown.

Excel Workbook
	P	Q
1	Date	Value
2	9-Jul	9
3	9-Jul	25
4	8-Jul	9
5	5-Jul	10
6	9-Jul	25
7
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q2	1. / Formula is =AND(P2=TODAY()-1,Q2>7)	Abc

 

[Marcelo Branco]

Hi,

Select Q1:Q5
CF > New Rule > Use a formula to determine...
Insert this formula
=AND($P1=TODAY()-1,$Q1>7)
Fill ---> yellow

HTH

M.

-EDIT-
I hadnt seen Peter's reply

M.

 

[danushka]

thanks a lot friends.it's work..

 

[Peter_SSs]

thanks a lot friends.it's work..

You won't really be sure of that until you open the file again tomorrow and check that these cells are no longer yellow.