Conditional Formating: Need Formula

K

krosado

New Member
Joined
Jan 27, 2011
Messages
31
I have a series of 5 columns, all of which will have dates attached.
<TABLE dir=ltr border=1 cellSpacing=1 borderColor=#333399 cellPadding=2 width=379><TBODY><TR><TD bgColor=#000080 height=20 width="13%">
Name​
​
​
​
​
​
​
​
​
​
​
</TD><TD bgColor=#99ccff height=20 width="20%">
A​
​
​
​
​
​
</TD><TD bgColor=#99ccff height=20 width="25%">
C​
​
​
​
​
​
</TD><TD bgColor=#99ccff height=20 width="21%">
D​
​
​
​
​
​
</TD><TD bgColor=#99ccff height=20 width="22%">
E​
​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
SF​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
3/31/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="25%">
3/31/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="21%">
3/31/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="22%">
3/11/2011​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
FL​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
3/18/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="25%">
4/6/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="21%">
4/6/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="22%">
4/7/2011​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
NY​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
3/17/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="25%">
4/6/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="21%">
4/6/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="22%">
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
TX​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
3/21/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="25%">
4/4/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="21%">
4/4/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="22%">
4/4/2011​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
NH​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
3/1/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="25%">
3/31/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="21%">
3/31/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="22%">
3/31/2011​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
IL​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
4/2/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="25%">
4/5/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="21%">
4/5/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="22%">
4/5/2011​
​
​
​
​
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
ME​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
</TD><TD bgColor=#ffffff height=19 width="25%">
</TD><TD bgColor=#ffffff height=19 width="21%">
</TD><TD bgColor=#ffffff height=19 width="22%">
</TD></TR><TR><TD bgColor=#ffffff height=19 width="13%">
MS​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="20%">
4/12/2011​
​
​
​
​
</TD><TD bgColor=#ffffff height=19 width="25%">
</TD><TD bgColor=#ffffff height=19 width="21%">
</TD><TD bgColor=#ffffff height=19 width="22%">
</TD></TR></TBODY></TABLE>
Is there a way that if the "Name" cell is not blank, then column A cell will turn yellow. Once a date is entered in column A, the cells color turns off?
~
Then if there is a date in Column A, Column C will highlight, and turn off when the cell has a date.
This step will be repeated for the remaining columns.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
is this what you want?
<TABLE style="WIDTH: 257pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=344 border=0 x:str><COLGROUP><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=4 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: navy" width=44 height=17>Name

</TD><TD class=xl25 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=75>A</TD><TD class=xl25 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=75>C</TD><TD class=xl25 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=75>D</TD><TD class=xl25 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=75>E</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=44 height=21>SF</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40633">3/31/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40633">3/31/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40633">3/31/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40613">3/11/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=44 height=21>FL</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40620">3/18/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40639">4/6/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40639">4/6/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40640">4/7/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=44 height=21>NY</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40619">3/17/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40639">4/6/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40639">4/6/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #333399 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=75> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=44 height=21>TX</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40623">3/21/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40637">4/4/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40637">4/4/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40637">4/4/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=44 height=21>NH</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40603">3/1/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40633">3/31/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40633">3/31/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40633">3/31/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=44 height=21>IL</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40635">4/2/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40638">4/5/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40638">4/5/2011</TD><TD class=xl22 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: white" width=75 x:num="40638">4/5/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #333399 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=44 height=21>ME</TD><TD class=xl23 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #333399 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=75> </TD><TD class=xl23 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #333399 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=75> </TD><TD class=xl23 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #333399 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=75> </TD><TD class=xl23 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #333399 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=75> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl27 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #666699 1pt solid; WIDTH: 33pt; BORDER-BOTTOM: #666699 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: white" width=44 height=22>MS</TD><TD class=xl28 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BORDER-LEFT: #333399; WIDTH: 56pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=75 x:num="40645">4/12/2011</TD><TD class=xl28 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #666699 1pt solid; mso-ignore: style; mso-pattern: auto none" width=75> </TD><TD class=xl28 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #666699 1pt solid; mso-ignore: style; mso-pattern: auto none" width=75> </TD><TD class=xl28 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399; BACKGROUND: yellow; BORDER-LEFT: #333399 0.5pt solid; BORDER-BOTTOM: #666699 1pt solid; mso-ignore: style; mso-pattern: auto none" width=75>

</TD></TR></TBODY></TABLE>

my conditional format was done by highlighting column B
=AND($A1<>"",B1="")
then I used the format painter on column B across to the other columns
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
520
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
117
Peter_SSs
Peter_SSs
F
  • Solved
Need formula for populating Date Status
Replies
7
Views
65
Joe4
Joe4
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
D
  • Question Question
conditional formatting of holidays
Replies
2
Views
102
daveyc18
D

Forum statistics

Threads
1,224,586
Messages
6,179,707
Members
452,939
Latest member
WCrawford

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