Conditional Formatting Guidance

Bennets04

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

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,674
Office Version
2019
Platform
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
58
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,525
Messages
5,511,823
Members
408,865
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top