Conditional Formatting Guidance

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
61
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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


Code:
=A1<TODAY()
<TODAY()< p>
set your font / fill as required.
 

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
61
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,732
Messages
5,833,375
Members
430,206
Latest member
Sami Gaid

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
Top