Excel IF statement to VBA

J

jhedges

Board Regular
Joined
May 27, 2009
Messages
208
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"
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Do you just want to check the last row the data is entered on or do you want to redo the entire sheet every time?
 
Upvote 0
Thanks for your reply...The sheet will change as I get updated information; therefore, will need to be updated/refreshed. A person could decide to cancel, at that point I would find that person and put a Y in column U and I would like the Column A cell to go to "INACTIVE" and the entire row to change to grey, like row 8, etc...
 
Upvote 0
So you only want to change one row at a time?
OK, give me a few minutes and I'll see what I can come up with.
 
Upvote 0
Not the tidiest of code and it's not tested, but it should get you going:-
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A" & Target.Row & ":U" & Target.Row).Interior.ColorIndex = xlNone
    If Range("K" & Target.Row) <> "" Then
        Range("A" & Target.Row) = "Expired"
        Exit Sub
    End If
    If Range("S" & Target.Row) <> "" Then
        Range("A" & Target.Row) = "Pending"
        Exit Sub
    End If
    If Range("U" & Target.Row) <> "" Then
        Range("A" & Target.Row) = "Inactive"
        Range("A" & Target.Row & ":U" & Target.Row).Interior.ColorIndex = 15
        Exit Sub
    End If
    Range("A" & Target.Row) = "WARNING"
    If Range("G" & Target.Row) = "BRB" Then
        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
                            If UCase(Range("K" & Target.Row)) = "" Then
                                If UCase(Range("S" & Target.Row)) = "" Then
                                    If UCase(Range("U" & Target.Row)) = "N" Then
                                        Range("A" & Target.Row) = "ACTIVE"
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
    If Range("G" & Target.Row) <> "BRB" Then
        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)) = "N" Then
                            If UCase(Range("K" & Target.Row)) = "" Then
                                If UCase(Range("S" & Target.Row)) = "" Then
                                    If UCase(Range("U" & Target.Row)) = "N" Then
                                        Range("A" & Target.Row) = "ACTIVE"
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub
 
Upvote 0
Thanks for your reply - I'm seeing an error message in the code when I change a data point. I get a complie error: End if without Block If.

Jason
 
Upvote 0
Sorry, got a bit "End If" happy. Try this:-
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A" & Target.Row & ":U" & Target.Row).Interior.ColorIndex = xlNone
    If Range("K" & Target.Row) <> "" Then
        Range("A" & Target.Row) = "Expired"
        Exit Sub
    End If
    If Range("S" & Target.Row) <> "" Then
        Range("A" & Target.Row) = "Pending"
        Exit Sub
    End If
    If Range("U" & Target.Row) <> "" Then
        Range("A" & Target.Row) = "Inactive"
        Range("A" & Target.Row & ":U" & Target.Row).Interior.ColorIndex = 15
        Exit Sub
    End If
    Range("A" & Target.Row) = "WARNING"
    If Range("G" & Target.Row) = "BRB" Then
        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
                        If UCase(Range("K" & Target.Row)) = "" Then
                            If UCase(Range("S" & Target.Row)) = "" Then
                                If UCase(Range("U" & Target.Row)) = "N" Then
                                    Range("A" & Target.Row) = "ACTIVE"
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
    If Range("G" & Target.Row) <> "BRB" Then
        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)) = "N" Then
                        If UCase(Range("K" & Target.Row)) = "" Then
                            If UCase(Range("S" & Target.Row)) = "" Then
                                If UCase(Range("U" & Target.Row)) = "N" Then
                                    Range("A" & Target.Row) = "ACTIVE"
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub
 
Upvote 0
Okay - so I copied my spreadsheet to a new workbook, deleted the IF/Then formulas I had in column A, right-clicked on the sheet1 tab and then on view code. I inserted your code and returned back to Excel. I am seeing the following:
  • I am trying to make the column A rows change based upon how the data is entered. I did mention that I would be doing one row at a time; however, initially it will need to check the entire spreadsheet, since I have deleted the IF/Then statements...Once that has completed then it will need to check each row as I make changes. Right now the VBA script is not initially checking the spreadsheet to make column A display the correct text to the data that is already there.
  • If I change column L to "N", the word "INACTIVE" is displayed in column A. This should be "WARNING"...
  • When I try to change column L back to "Y", the spreadsheet crashes. I get a error: Method Colorindex of Object Interior Failed...
I'm very new at VBA, could it be the order of the code? It also seems to take a little bit of time to calculate the spreadsheet. I can see the bottom lefthand side of the sheet calculating...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
197
Tara_A_Data
T
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
295
Cquake
C

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

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