Excel Error

[jhedges]

I am using Windows XP sp3 and Excel 07. Below is my spreadsheet and a VBA code to display a calendar.

I have this spreadsheet setup as a table (old list feature in 03) and when I get to the end of the table row and press tab to get the spreadsheet to enter a new row to the table I get an error message stating MS Excel has encountered a problem and needs to close. Do you want to send an error report or not? It then proceeds to recover itself. I have repaired my Office installation and tried to create a new spreadsheet (saved as a different name) all to no avail. I'm wondering if anyone has had a similar issue and what was done to correct this?

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 93px"><COL style="WIDTH: 142px"><COL style="WIDTH: 150px"><COL style="WIDTH: 108px"><COL style="WIDTH: 84px"><COL style="WIDTH: 89px"><COL style="WIDTH: 92px"><COL style="WIDTH: 62px"><COL style="WIDTH: 78px"><COL style="WIDTH: 83px"><COL style="WIDTH: 83px"><COL style="WIDTH: 82px"><COL style="WIDTH: 84px"><COL style="WIDTH: 83px"><COL style="WIDTH: 83px"><COL style="WIDTH: 87px"><COL style="WIDTH: 83px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Status</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Member FName</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Member LName</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Personnel #</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Join Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Exp. Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Payroll Submitted</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Site</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Trainer</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Session1 Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">S1 Status</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Session2 Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">S2 Status</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Session3 Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">S3 Status</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Session4 Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">S4 Status</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #c5be97">New Member</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">09/26/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5be97">10/26/2011</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=IF((AND(F2>0,F2<TODAY(),G2="")),"EXPIRED",IF((AND(F2>TODAY(),G2="YES")),"Active",IF((AND(F2>TODAY(),G2="")),"New Member","")))</TD></TR><TR><TD>F2</TD><TD>=IF(ISBLANK(E2),"",E2+30)</TD></TR><TR><TD>K2</TD><TD>=IF(AND(WEEKNUM(TODAY())>WEEKNUM(E2),ISBLANK(J2)),"S1 PAST DUE","")</TD></TR><TR><TD>M2</TD><TD>=IF(AND(WEEKNUM(TODAY())>WEEKNUM(E2)+1,ISBLANK(L2)),"S2 PAST DUE","")</TD></TR><TR><TD>O2</TD><TD>=IF(AND(WEEKNUM(TODAY())>WEEKNUM(E2)+2,ISBLANK(N2)),"S3 PAST DUE","")</TD></TR><TR><TD>Q2</TD><TD>=IF(AND(WEEKNUM(TODAY())>WEEKNUM(E2)+3,ISBLANK(P2)),"S4 PAST DUE","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Private Sub Calendar1_Click()
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = "mm/dd/yyyy"
    ActiveCell.Select
    Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("E2:E200,J2:J200,L2:L200,N2:N200,P2:P200"), Target) Is Nothing Then
        Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
        Calendar1.Top = Target.Top + Target.Height
        Calendar1.Visible = True
        ' select Today's date in the Calendar
        Calendar1.Value = Date
    ElseIf Calendar1.Visible Then Calendar1.Visible = False
    End If
End Sub

 

[jhedges]

Re: Excel Error - Conditional Formatting

Okay - the problem is with the conditional formatting I was using in the cells. Seems to be an MS issue. Once this is removed, then Excel will populate the next row in the table when called for. So, next question is:

Can I use VBA to do the same thing as conditional formatting?

If $A2 ="EXPIRED" then the cell should go to Black with white text

If hidden column $K2= "S1 Past Due", then cell $J2 should go Red, no text
If hidden column $M2= "S2 Past Due", then cell $L2 should go Red, no text
If hidden column $O2= "S3 Past Due", then cell $N2 should go Red, no text
If hidden column $Q2= "S4 Past Due", then cell $P2 should go Red, no text

If hidden column $K2= "S1 Past Due", then cell $A2:$I2,$L2:$P2 should go Yellow, text to black.
If hidden column $M2= "S2 Past Due", then cell $A2:$J2,$N2:$P2 should go Yellow, text to black.
If hidden column $O2= "S3 Past Due", then cell $A2:$L2,$P2 should go Yellow, text to black.
If hidden column $Q2= "S4 Past Due", then cell $A2:$N2 should go Yellow, text to black.

Any help would be much appreciated...