Excel IF statement to VBA

[jhedges]

BRTC Database

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 107px"><COL style="WIDTH: 80px"><COL style="WIDTH: 112px"><COL style="WIDTH: 89px"><COL style="WIDTH: 113px"><COL style="WIDTH: 114px"><COL style="WIDTH: 124px"><COL style="WIDTH: 114px"><COL style="WIDTH: 85px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>F</TD><TD>G</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>S</TD><TD>U</TD></TR><TR style="HEIGHT: 59px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">STATUS</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">STAFF</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">MEMBERSHIP TYPE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">EXPIRATION DATE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">DOOR ACCESS</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">SAFETY ORIENTATION</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">EQUIPMENT ORIENTATION</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">PAYROLL FORM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">PENDING</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">CANCELED</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">EXPIRED</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">06/08/08</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">EXPIRED</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">11/08/08</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">NM</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">06/13/12</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">INACTIVE</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">JH</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">BRB</TD><TD style="BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">N</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD><TD style="BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-FAMILY: Frutiger 45 Light; COLOR: #808080">Y</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00b050; FONT-FAMILY: Frutiger 45 Light; COLOR: #ffffff; FONT-WEIGHT: bold">ACTIVE</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">BRB</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">JH</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">F&HCIC</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">Y</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</TD><TD style="FONT-FAMILY: Frutiger 45 Light"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Frutiger 45 Light; FONT-WEIGHT: bold">N</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((U2="Y"),"INACTIVE",IF((AND(ISBLANK(K2),S2="PENDING")),"PENDING",IF((AND(ISBLANK(K2),L2="Y",M2="Y",N2="Y",O2="Y",U2="N")),"ACTIVE",(IF((AND(K2>0,K2<TODAY(),U2="N")),"EXPIRED",IF((AND(G2="BRB",L2="N",M2="N",N2="N",O2="N")),"WARNING",IF((AND(K2>TODAY(),U2="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A3</TD><TD>=IF((U3="Y"),"INACTIVE",IF((AND(ISBLANK(K3),S3="PENDING")),"PENDING",IF((AND(ISBLANK(K3),L3="Y",M3="Y",N3="Y",O3="Y",U3="N")),"ACTIVE",(IF((AND(K3>0,K3<TODAY(),U3="N")),"EXPIRED",IF((AND(G3="BRB",L3="N",M3="N",N3="N",O3="N")),"WARNING",IF((AND(K3>TODAY(),U3="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A4</TD><TD>=IF((U4="Y"),"INACTIVE",IF((AND(ISBLANK(K4),S4="PENDING")),"PENDING",IF((AND(ISBLANK(K4),L4="Y",M4="Y",N4="Y",O4="Y",U4="N")),"ACTIVE",(IF((AND(K4>0,K4<TODAY(),U4="N")),"EXPIRED",IF((AND(G4="BRB",L4="N",M4="N",N4="N",O4="N")),"WARNING",IF((AND(K4>TODAY(),U4="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A5</TD><TD>=IF((U5="Y"),"INACTIVE",IF((AND(ISBLANK(K5),S5="PENDING")),"PENDING",IF((AND(ISBLANK(K5),L5="Y",M5="Y",N5="Y",O5="Y",U5="N")),"ACTIVE",(IF((AND(K5>0,K5<TODAY(),U5="N")),"EXPIRED",IF((AND(G5="BRB",L5="N",M5="N",N5="N",O5="N")),"WARNING",IF((AND(K5>TODAY(),U5="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A6</TD><TD>=IF((U6="Y"),"INACTIVE",IF((AND(ISBLANK(K6),S6="PENDING")),"PENDING",IF((AND(ISBLANK(K6),L6="Y",M6="Y",N6="Y",O6="Y",U6="N")),"ACTIVE",(IF((AND(K6>0,K6<TODAY(),U6="N")),"EXPIRED",IF((AND(G6="BRB",L6="N",M6="N",N6="N",O6="N")),"WARNING",IF((AND(K6>TODAY(),U6="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A7</TD><TD>=IF((U7="Y"),"INACTIVE",IF((AND(ISBLANK(K7),S7="PENDING")),"PENDING",IF((AND(ISBLANK(K7),L7="Y",M7="Y",N7="Y",O7="Y",U7="N")),"ACTIVE",(IF((AND(K7>0,K7<TODAY(),U7="N")),"EXPIRED",IF((AND(G7="BRB",L7="N",M7="N",N7="N",O7="N")),"WARNING",IF((AND(K7>TODAY(),U7="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A8</TD><TD>=IF((U8="Y"),"INACTIVE",IF((AND(ISBLANK(K8),S8="PENDING")),"PENDING",IF((AND(ISBLANK(K8),L8="Y",M8="Y",N8="Y",O8="Y",U8="N")),"ACTIVE",(IF((AND(K8>0,K8<TODAY(),U8="N")),"EXPIRED",IF((AND(G8="BRB",L8="N",M8="N",N8="N",O8="N")),"WARNING",IF((AND(K8>TODAY(),U8="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A9</TD><TD>=IF((U9="Y"),"INACTIVE",IF((AND(ISBLANK(K9),S9="PENDING")),"PENDING",IF((AND(ISBLANK(K9),L9="Y",M9="Y",N9="Y",O9="Y",U9="N")),"ACTIVE",(IF((AND(K9>0,K9<TODAY(),U9="N")),"EXPIRED",IF((AND(G9="BRB",L9="N",M9="N",N9="N",O9="N")),"WARNING",IF((AND(K9>TODAY(),U9="N")),"ACTIVE","")))))))</TD></TR><TR><TD>A10</TD><TD>=IF((U10="Y"),"INACTIVE",IF((AND(ISBLANK(K10),S10="PENDING")),"PENDING",IF((AND(ISBLANK(K10),L10="Y",M10="Y",N10="Y",O10="Y",U10="N")),"ACTIVE",(IF((AND(K10>0,K10<TODAY(),U10="N")),"EXPIRED",IF((AND(G10="BRB",L10="N",M10="N",N10="N",O10="N")),"WARNING",IF((AND(K10>TODAY(),U10="N")),"ACTIVE","")))))))</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

My spreadhseet is A1:Y190 setup as a table and will grow in rows.

I included some of the columns along with the formulas.

I am trying to make the column A rows change based upon how the data is entered and was trying formulas; however, have ran into limitations and was thinking VBA could help; however, I'm not sure how to create code in VBA and would appreciate some help:

If Membership Type(G) = "BRB":
Looking at columns L,M,N,O as all = "Y", Not expired(K), Pending(S) <> "pending" and Cancelled(U) = "N" then display "ACTIVE".

If LMNO have "N" in any of the columns with the above criteria, then display "WARNING".

Regardless of the above:
If Expired(K), then display "EXPIRED"

If Pending(S), then display "PENDING"

If Cancelled(U), then display "INACTIVE"

If Membership Type(G) <> "BRB", but equal to "F&HCIC", "GO", "MBC", "MVIC" or "WHBC":
Looking at columns L,M,N as all = "Y", O="N", Not expired(K), Pending(S) <> "pending" and Cancelled(U) = "N" then display "ACTIVE".

If L,M,N have "N" in any of the columns with the above criteria, then display "WARNING".

Regardless of the above:
If Expired(K), then display "EXPIRED"

If Pending(S), then display "PENDING"

If Cancelled(U), then display "INACTIVE"

 

[jhedges]

This is what I have.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Finished
Stop
Range("A" & Target.Row).Font.Color = 16777215
If IsDate(Range("K" & Target.Row)) Then
    Range("A" & Target.Row).Formula = "EXPIRED"
    Range("A" & Target.Row).Interior.Color = 0
    Exit Sub
End If
If IsDate(Range("S" & Target.Row)) Then
    Range("A" & Target.Row).Formula = "PENDING"
    Range("A" & Target.Row).Interior.Color = 16777215
    Exit Sub
End If
If IsDate(Range("U" & Target.Row)) Then
    Range("A" & Target.Row).Formula = "INACTIVE"
    Range("A" & Target.Row).Interior.Color = 14211288
    Range("A" & Target.Row).Font.Color = 8421504
    Exit Sub
End If
If UCase(Range("G" & Target.Row).Text) = "BRB" Then
    If Application.WorksheetFunction.CountIf(Range("L" & Target.Row & ":O" & Target.Row), "Y") = 4 Then
        Range("A" & Target.Row).Formula = "ACTIVE"
        Range("A" & Target.Row).Interior.Color = 5287936
    Else
        Range("A" & Target.Row).Formula = "WARNING"
        Range("A" & Target.Row).Interior.Color = 255
    End If
Else
    If InStr(1, "F&HCICGOMBCMVICWHBC", UCase(Range("G" & Target.Row).Text)) > 0 Then
        If Application.WorksheetFunction.CountIf(Range("L" & Target.Row & ":N" & Target.Row), "Y") = 3 Then
            Range("A" & Target.Row).Formula = "ACTIVE"
            Range("A" & Target.Row).Interior.Color = 5287936
        Else
            Range("A" & Target.Row).Formula = "WARNING"
            Range("A" & Target.Row).Interior.Color = 255
        End If
    End If
End If
Finished:
End Sub

When I change something on the worksheet it takes me to the debugger...no error message. Just get the crash when I try to use the code without the stop. I have also noticed Excel crashes when I'm using conditional formatting, within Excel 07 (not VBA). I have other workbooks that use conditional formatting without issue...I have tried to repiar my Office installation and created a new worksheet thinking maybe something was corrupt...

When I re-opened the recovered worksheet the datapoint I had changed had the correct entry in column A. Any ideas to help with the crashing issue? Also, is there away to force the VBA to update the entire worksheet based upon how the current data is set, instead of going back and changing a datapoint and then putting it back to force the script to run...?

possibly something like this:

application.calculation=xlmanual
 
 
application.calculation=xlautomatic

 

[Blade Hunter]

No, Calculation is for formulas and the lines you posted just turn auto on and off.

You don't need to change a datapoint though, you can just hit F2 then enter on a cell and it will refire the code.

You could easily write a small macro to run through the cells to do this. Something like:

For X = 2 to range("G" & rows.count).end(xlup).row
Range("G" & X).Formula = Range("G" & X).text
Next

Careful though, if you have tons and tons of rows it's gonna take a little while.

 

[jhedges]

Thanks - any idea on the crashing issue?

 

[Blade Hunter]

None unfortunately, without being there and seeing your machine I wouldn't know where to start. Do you have the latest updates and everything on your machine?

 

[jhedges]

As far as I know...we get updates pushed from our update servers here at work. I was thinking about trying to unistall/reinstall Offcie to see if that corrects the issue...

 

[jhedges]

I have another question, which is at the thread below:

http://www.mrexcel.com/forum/showthread.php?t=582007

I was wondering if you had any advice?

 

[Blade Hunter]

I am tied up in meetings for the next 4 hours but will check it out after I get back.

Cheers

Dan

 

[jhedges]

Thanks - just an update on this thread. I completely uninstalled/reinstalled Office then opened the worksheet. It still crashes Excel and forces it to restart/recover the worksheet. Once it opens again, the VBA script has worked and colored the cell correctly. Each time I change a data point in the worksheet it crashes/recovers and the VBA script has worked.

When I insert a new row in the table, which doesn't have any data entered yet, it works fine. Could it be just to resource intensive to handle the request with the data in the rows?

Thanks
Jason

 

[RichardMGreen]

As a quick test, comment out the lines that change the row colour.
I've had problems before with things like that.

 

[jhedges]

Not sure what you mean by comment out the lines that change color?