using conditional formatting

G

Green1972

Board Regular
Joined
Dec 8, 2008
Messages
161
Hello.
Ok what i have is a range of cells containing different dates (row E100 to BB100). In cell B20 i have another date. What i want to do is use conditional formatting to highlight the cells in row E100 to BB100 that are within 7 days of the date in cell B20. Is it even possibel.:stickouttounge:
 
Sort by date Sort by votes

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
<TABLE style="WIDTH: 760pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1020><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" span=20 width=51><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 width=51>31.7.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>19.9.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>1.7.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>21.4.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>26.7.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>5.7.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>12.7.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>18.8.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>19.9.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>9.12.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>14.10.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>24.4.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>31.8.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>9.8.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>23.9.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>7.7.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>26.4.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>16.8.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>14.8.06</TD><TD style="BORDER-BOTTOM: blue 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=51>22.8.06</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 76pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=102><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" span=2 width=51><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #330cc4 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 14.25pt; BORDER-TOP: #330cc4 0.5pt solid; BORDER-RIGHT: #330cc4 0.5pt solid" class=xl67 height=19 width=102 colSpan=2>Target date</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #330cc4 0.5pt solid; BORDER-LEFT: #330cc4 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #330cc4 0.5pt solid" class=xl69 height=19 colSpan=2>20.9.06</TD></TR></TBODY></TABLE>
Hi The top row are the dates in question and the Traget date is on it's own. Sorry about the cut and paste but it's the only way i know how the post.
 
Upvote 0
Green1972 said:
<TABLE style="WIDTH: 760pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1020 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" span=20 width=51><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=51 height=19>31.7.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>19.9.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>1.7.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>21.4.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>26.7.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>5.7.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>12.7.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>18.8.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>19.9.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>9.12.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>14.10.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>24.4.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>31.8.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>9.8.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>23.9.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>7.7.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>26.4.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>16.8.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>14.8.06</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: blue 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>22.8.06</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 76pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=102 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" span=2 width=51><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl67 style="BORDER-RIGHT: #330cc4 0.5pt solid; BORDER-TOP: #330cc4 0.5pt solid; BORDER-LEFT: #330cc4 0.5pt solid; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=102 colSpan=2 height=19>Target date</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #330cc4 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #330cc4 0.5pt solid; BORDER-BOTTOM: #330cc4 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" colSpan=2 height=19>20.9.06</TD></TR></TBODY></TABLE>
Hi The top row are the dates in question and the Traget date is on it's own. Sorry about the cut and paste but it's the only way i know how the post.
Click to expand...
Well, none of those dates meet the condition!

If the target date is (m/d/y) 9/20/2006 and you want dates that are within 7 days of that date, the range would be from 9/13/2006 to 9/20/2006.

None of the dates you posted are within that range.
 
Upvote 0
Hi sorry for the delay. I must have not been to clear but i wanted any date within seven days (above or below) of the target date to be highlighted. Sorry for the confussion. I hope you can help.:rofl::laugh:
 
Upvote 0
T. Valko said:
I don't have Excel 2010 but this should be the same as it is in Excel 2007.

Select the *entire* range E100:BB100 starting from cell E100.
Cell E100 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The


formula will be relative to the active cell.
  • Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
  • Enter this formula in the box below:
    =AND(E100>=$B$20-7,E100<=$B$20)
  • Click the Format button
  • Select the desired style(s)
  • OK out
Click to expand...

Green1972 said:
Hi sorry for the delay. I must have not been to clear but i wanted any date within seven days (above or below) of the target date to be highlighted. Sorry for the confussion. I hope you can help.:rofl::laugh:
Click to expand...
Ok, then just a slight tweak of the formula should do the trick.

=AND(E100>=$B$20-7,E100<=$B$20+7)
 
Upvote 0
Hello thank you for your help but i have tried it and it does not work. Is there any other factors that could effect it working
 
Upvote 0
Green1972 said:
Hello thank you for your help but i have tried it and it does not work. Is there any other factors that could effect it working
Click to expand...
Your dates may not be true Excel dates. They may be text strings that look like dates.

In Excel a date is really just a number formatted to look like a date.

You can test the cell entry to see if it's a true date or not.

A1 = some date

=ISNUMBER(A1)

If A1 contains a true Excel date then that formula will return TRUE.
 
Upvote 0
Hello again. Thank for that but i dont understand what it is you want me to do. The dates that i have shown you are generated from a formula that uses the average number of days between when a lotto ball is drawn since the draws started and then looks at the lats date it was drawn and predicts the date from then. Each cell has been formated to as a date. Not sure if that helps.
 
Upvote 0
Green1972 said:
Hello again. Thank for that but i dont understand what it is you want me to do. The dates that i have shown you are generated from a formula that uses the average number of days between when a lotto ball is drawn since the draws started and then looks at the lats date it was drawn and predicts the date from then. Each cell has been formated to as a date. Not sure if that helps.
Click to expand...
Here's a small sample file that demonstrates this.

zzzGreen1972.xlsx 10kb

http://cjoint.com/?AFspZ3m8vVU
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
AND conditional formatting
2
Replies
10
Views
445
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Conditional Formatting for a Date
Replies
6
Views
269
Joe4
Joe4
J
  • Question Question
Conditional formatting of single column of pivot table
Replies
8
Views
310
Peter_SSs
Peter_SSs
O
  • Question Question
Need help with formulas for conditional formatting
Replies
1
Views
493
6StringJazzer
6StringJazzer
O
  • Question Question
Formula for conditional formatting.
Replies
2
Views
417
felixstraube
F

Forum statistics

Threads
1,224,582
Messages
6,179,670
Members
452,936
Latest member
anamikabhargaw

Share this page

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