Conditional Formatting Guidance

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
65
Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I am after some guidance regarding trying to set Conditional to check the dates on my spreadsheet and return back as RED if that date has passed<o:p></o:p>
Now the issue I have is I have used Vlookup’s and other formulas to calculate the dates so when I add the formula for Conditional Formatting it doesn’t recognise the date due to the formula in the cell... see below<o:p></o:p>
.....What I want is If 29/04/2010 is less than today date then format the cell<o:p></o:p>
This is the Imbedded formula..<o:p></o:p>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 425pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=567><TBODY><TR style="HEIGHT: 16.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 141pt; PADDING-RIGHT: 5.4pt; HEIGHT: 16.5pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; PADDING-TOP: 0cm" width=188>=VLOOKUP(B5,$IQ$4:$IR$1298,2) (Day of the Week)<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 141pt; PADDING-RIGHT: 5.4pt; HEIGHT: 16.5pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; PADDING-TOP: 0cm" width=188>=B6-1 (The date I want to format)<o:p></o:p>
</TD></TR></TBODY></TABLE>
Is there a way of changing the formula in these date cells so it will recognise the data in the cell and not the formula?<o:p></o:p>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: -2; POSITION: absolute; MARGIN-TOP: 110.25pt; WIDTH: 252pt; HEIGHT: 238.5pt; VISIBILITY: visible; MARGIN-LEFT: 114pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" id=Picture_x0020_1 type="#_x0000_t75" o:spid="_x0000_s1027"><v:imagedata src="file:///C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.png" cropright="32816f" cropbottom="3756f" croptop="20593f" o:title=""></v:imagedata></v:shape><v:shape style="Z-INDEX: -1; POSITION: absolute; MARGIN-TOP: 116.25pt; WIDTH: 135.75pt; HEIGHT: 222.75pt; VISIBILITY: visible; MARGIN-LEFT: -51.75pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" id=Picture_x0020_4 type="#_x0000_t75" o:spid="_x0000_s1026"><v:imagedata src="file:///C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image003.png" cropright="47219f" cropbottom="3911f" croptop="21576f" o:title=""></v:imagedata></v:shape>Thanks again<o:p></o:p>
Steve
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Welcome to the board!

It should work without changing anything, conditional formats look at the result, not the formula.

select the range of cells you want the condition applied to, go to conditional formatting and enter the formula


PHP:
=A1<TODAY()
<TODAY()< p>
set your font / fill as required.
 
Upvote 0
JasonB,

Thank you so much, It worked a treat!

I wont tell you how long i have spent trying to do that lol...

Thanks again
Steve
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top