V-Look or IF Statement help

[zeppelinmike]

Hi everyone, I have a yearly calendar (example shown below) which is colour coded. I want Excel to insert the dates which match 'red' into cells in a new sheet next to my calendar. This is for me to keep track and print off all dates which are in red e.g. urgent. NOt sure if this is a IF statement or V-Lookup.

Any help with this would be much appreciated!


<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=283 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" span=2 width=69><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl79 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 213pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=283 colSpan=4 height=18>M A Y</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ff9900" height=21 x:num="40665">02/05/2011</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: red" x:num="40672" x:fmla="=A2+7">09/05/2011</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40679" x:fmla="=B2+7">16/05/2011</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40686" x:fmla="=C2+7">23/05/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: aqua" height=17 x:num="40666">03/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40673" x:fmla="=A3+7">10/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40680" x:fmla="=B3+7">17/05/2011</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40687" x:fmla="=C3+7">24/05/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: aqua" height=17 x:num="40667">04/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40674" x:fmla="=A4+7">11/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40681" x:fmla="=B4+7">18/05/2011</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40688" x:fmla="=C4+7">25/05/2011</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: aqua" height=18 x:num="40668">05/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40675" x:fmla="=A5+7">12/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40682" x:fmla="=B5+7">19/05/2011</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40689" x:fmla="=C5+7">26/05/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: aqua" height=21 x:num="40669">06/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40676" x:fmla="=A6+7">13/05/2011</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40683" x:fmla="=B6+7">20/05/2011</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" x:num="40690" x:fmla="=C6+7">27/05/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ff99cc" height=17 x:num="40670">07/05/2011</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff99cc" x:num="40677" x:fmla="=A7+7">14/05/2011</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff99cc" x:num="40684" x:fmla="=B7+7">21/05/2011</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff99cc" x:num="40691" x:fmla="=C7+7">28/05/2011</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ff99cc" height=18 x:num="40671">08/05/2011</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc" x:num="40678" x:fmla="=A8+7">15/05/2011</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc" x:num="40685" x:fmla="=B8+7">22/05/2011</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc" x:num="40692" x:fmla="=C8+7">29/05/2011</TD></TR></TBODY></TABLE>

 

[Dannyh1]

This can only be done via code as formula will not recognise colours.

 

[Steeviee]

I heard that in Excel 2007 & 2010, that this can be done...anyone know? I'm looking forward to my company finally updating us to a newer version.

 

[Dannyh1]

ah, my workplace still stuck in dark ages (Excel2000!). That sounds like a great feature!