Conditional formatting Formula help please

[salmonchild]

Hi all,

Could someone please help me with a formula for conditional formatting?

I need to format the whole row based on a date, whereby if the date in column BI is within a week either side of today's date (which is fixed on another sheet in cell 'Legend and Pivots'!D1) the text of the row is formatted red or similar. Have tried to express as a formula but failed.

Thank you

salmon

 

[Somendra Misra]

Hi Salmon,

See the attached file. Sheet1 Cell BI2 has the dat and row 2 is formatted. If this meets your requirement, just extend the range.

Regards,

 

[ThrottleWorks]

Hi,

Will this work ? Kindly confirm.

P.S. - used randbetween function to genarate dates, forgot to paste special. Gave me jitters.

 

[salmonchild]

structure example attached Somendra.

@ThrottleWorks it works partially, I can see how the helper colum works and how the CF should apply however if I can avoid helper columns that would be good and also the application of the CF in your example does not work as it should (this may be a a compatibility issue- I am running 2013) please see screencap for reference.

I was thinking originally along the lines of:

=OR(BI2>('Legend and Pivots'!D1-7),BI2<('Legend and Pivots'!D1+7))

 

[salmonchild]

screencap attached this time.

 

[Somendra Misra]

@salmonchild

See the file attached on my earlier comment.

Regards,

 

[salmonchild]

@Somendra Misra and @ThrottleWorks , thank you both for your input and solutions.

It looks like I was just a couple of iterations away from your solution Somendra- it has been a long day with many problems so I was perhaps too keen in giving upn and asking on here!

Excel inner peace has been restored!

 

[Somendra Misra]

@salmonchild

Welcome back anytime.

Regards,