Conditional Formatting

[Pete]

I have designed a spreadsheet to act as my Risk Assessment Register.
It contains the weekending dates across the top of the sheet in B1:Z1,
this is done by B1=08/01/10, C1=B1+7 and autofill to Z1. Obviously
there will be 52 columns as there is 52 weeks in the year, I have only
gone to Z1 for the purpose if this question.

I have the date for risk assessment review down the left hand column
in A2:A30

What I would like to do is conditionally format the cell that
corresponds to the review date (A2:A30) depending on the weekending
date in B1:Z1

Example

If A2=16/01/10 then I would like to conditionally format the cell in
Row 2 depending on which of the Cells in B1:Z1 is closest to the date
in A2 in my example it would be cell D2 requiring the format.

Thanks in advance

Pete

 

[T. Valko]

is closest to the date

Based on your expected result then you want the closest date that is greater
than or equal to the date in A2.

Select the range B2:Z2

Conditional formatting
Formula:

=AND($A2>0,B$1=MIN(IF($B$1:$Z$1>=$A2,$B$1:$Z$1)))

If you test this formula on the worksheet then it has to be array entered.

Array formulas entered on the worksheet need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL
key and the SHIFT key then hit ENTER.

 

[Pete]

Based on your expected result then you want the closest date that is greater
than or equal to the date in A2.

Select the range B2:Z2

Conditional formatting
Formula:

=AND($A2>0,B$1=MIN(IF($B$1:$Z$1>=$A2,$B$1:$Z$1)))

If you test this formula on the worksheet then it has to be array entered..

Array formulas entered on the worksheet need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL
key and the SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP










- Show quoted text -

Brilliant, works perfectly thank you.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Based on your expected result then you want the closest date that is
greater
than or equal to the date in A2.

Select the range B2:Z2

Conditional formatting
Formula:

=AND($A2>0,B$1=MIN(IF($B$1:$Z$1>=$A2,$B$1:$Z$1)))

If you test this formula on the worksheet then it has to be array entered.

Array formulas entered on the worksheet need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL
key and the SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP










- Show quoted text -

Brilliant, works perfectly thank you.