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"

 

[RichardMGreen]

In some semblance of order:-
The routine only checks the row that is currently being edited.
You could, initially, put the routine into a loop to check each row and then carry on as normal.
If you go through each IF statement in the routine, I may have made a small error in the ordering of things.
Does the error appear on the first line of the code or somewhere further down?
To get round the calculations taking forever, put the following as the first of code just after the Private Sub line:=
application.calculation=xlmanual

and then put:-
application.calculation=xlautomatic
just before the End Sub.

 

[Blade Hunter]

Richard, maybe you can get rid of some of those IF's like this:

        If UCase(Range("L" & Target.Row)) = "Y" Then
            If UCase(Range("M" & Target.Row)) = "Y" Then
                If UCase(Range("N" & Target.Row)) = "Y" Then
                    If UCase(Range("O" & Target.Row)) = "Y" Then

Can be written as:

If UCase(Application.WorksheetFunction.CountIf(Range("L" & Target.Row & ":O" & Target.Row), "Y")) = 4 Then

 

[Blade Hunter]

In fact, you could concat the whole range and check if the UCase text is WARNINGBRBYYYYN, then it is only 1 if statement.

 

[Blade Hunter]

From looking at the original requirement further, I believe this will do exactly what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Finished
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

 

[jhedges]

Blade Hunter,

Thanks for your reply! Looking at your code should I leave my original formulas in Column A? If so, then I was trying to get rid of the formulas as it was getting to big for the IF/Then Statements to work in Excel. I was hoping to use VBA to populate the rows in column A based upon how the data was entered.

When I run your code without my formulas it doesn't work...

Thanks
Jason

 

[Blade Hunter]

Put a stop on line one of the code, Make a change on the sheet and it will take you to debug mode, step through one line at a time using F8 and see where it is failing. This code works perfectly for me, it enters the formula result as text in to column A for the row you have modified. I also made it shade the cell and colour the text according to your example.

 

[jhedges]

So, I should leave the if/then formulas in column A of my spreadsheet?

Thanks
Jason

 

[Blade Hunter]

So, I should leave the if/then formulas in column A of my spreadsheet?

Thanks
Jason

No, the code will overwrite column A anyway, just make a change on one of the other cells and it should update column A for that row.

Make sure the code is at the worksheet level

 

[jhedges]

Ok - I pasted the code via the following - right clicked worksheet tab and then on view code.

Before I placed the stop - I changed a datapoint and the worksheet calculated for a few seconds and then caused Excel to crash, asking me if I wanted to send an error report and recover my worksheet....

I placed the stop and did not get any error messages...

Removed the stop and made another change and Excel crashed again....

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?

Possibly add the following:

application.calculation=xlmanual


application.calculation=xlautomatic

 

[Blade Hunter]

Hmmmmm, most odd. ok remove the stop and insert a new line below on error goto finished.

On that new line just type STOP

Then try changing something on the sheet again.

Cheers

Dan