Internship Recruitment VBA Programming

[aegliveinterns]

Hello,<?xml:namespace prefix = o /><o></o>
<o></o>

I'm creating an internship recruitment roster, and I'm looking to program the sheet to do a few things automatically:<o></o>
<o></o>

1) Every intern is labeled with a status letter under a "Status" column, such as A for hire, for F for fail. I would like to program the sheet to automatically assign the row of a given letter the color I designate, and put a border around it. Unfortunately, there are more letters in use than conditional formatting can keep up with.<o></o>
<o></o>

2) Every time I enter a new person in the system (by typing in their status letter), I would like the sheet to automatically sort itself in a given range (all cells except my headers, which range in row from 1:A-1:N

 

[lenze]

Hi and welcome to the board!!
One change event should do it all!! Assuming status is in Column "N" and you are sorting on Column "A"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 14 Then Exit Sub  'Column "N" Change as needed
Dim icol As Variant
Select Case UCase(Target)
 Case "A": icol = 6
 Case "F": icol = 4
 'etc
 Case Else: icol = xlNone
End Select
Cells(Target.Row, "A").Resize(1, 14).Interior.ColorIndex = icol
Range("A1").CurrentRegion.Sort Key1:=Range("$A$2"), Order1:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Paste this code in the sheet module. RightClick the sheet tab and choose "View Code"
lenze

 

[aegliveinterns]

Hi Lenze,

Thanks so much for getting back to me so quickly! The code you gave me only got me success with the letter A unfortunately. It must be my fault since I'm so new to all of this, but I thought it might help if I told you exactly what I'm trying to do:

These are the statuses possible for intern candidates:

A= hire = row is bright green
B= pass interview = row is light green
C= questionable pass interview = row is light orange
E=potential candidate = row is yellow
F=failed candidate = row is red

Also if possible I'd like rows to become bordered from Column A-M when I enter a new letter in the status column (Column A). Also, I only sort by Status (column A) and then by name (Column B). However, the sorting has to start at row 9 because my header columns extend from row 1 to 8.

Is this all possible? And how can I go about educating myself in the future about VBA? Thank you so much for all of your help.

 

[lenze]

Ok: Let's go 1 step at a time. First, in a blank workbook, open the VBE(ALT+F11). Choose Insert>Module. Paste this code in the white panel

Sub ShowColorIndex()
   Dim cl As Range
    Dim x As Integer
    x = 0
    Range("$A$3:$A$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .RowHeight = 20
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$C$4:$C$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$E$4:$E$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$G$4:$G$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$D$1").Select
    Selection = "COLORINDEX"
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlCenter
    Selection.ColumnWidth = 17.5
End Sub

Return to Excel(ALT+Q)
Open the Macro dialog(ALT+F8) Select the macro ShowColorIndex and choose run. This will give you the ColorIndex Numbers for your computer. Next Let's look at the First Part of the code. We have to change the references to fit you needs.I will comment the code for you

Private Sub Worksheet_Change(ByVal Target As Range)
'Only run if ONE cell is changed
If Target.Count > 1 Then Exit Sub 
Only run if the change is in Column "A"
If Target.Column <> 1 Then Exit Sub 
'Declare the variable iCol for Interior Color
Dim icol As Variant
'Select the Value of the cell(Target) that was changed
Select Case UCase(Target)
'If "A" then set iCol to 4 which is Bright Green
Case "A": icol = 4
'If "B" then set iCol to 35 Light Green
Case "B": icol = 35
 'etc for other letters. Get the numbers of the colors you need from the above macro
'If the Target value is not found, then set iCol to Nothing
 Case Else: icol = xlNone
End Select
'Resize the Target to 13 Columns and apply the iCol that was set in the Select Case
Target.Resize(1, 13).Interior.ColorIndex = icol
'Determine the range to sort and then perform the sort
Range("A9:M"& Cells(Rows.Count,"M").End(xlUp).Row).Sort Key1:=Range("$A$9"), Order1:=xlAscending, Key2:=Range("$B$9"),Header:= _
        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

HTH
lenze

 

[lenze]

Just noticed I missed an ' in the 3rd line of my example, so you need to edit it

'Only run if the change is in Column "A"

lenze

 

[aegliveinterns]

I tried that too, but when I press Alt+F8 to run the macro, it doesn't appear in the menu. What should I do?

 

[lenze]

You have missed the point. If you place the macro in the sheet module (RightClick the sheet tab and choose "View Code") the macro runs automatically whenever you enter or change a cell in Column "A". You don't see it because it is Private. It's called an Event Procedure and the event here is Changing a cell. If you have info already entered and need to highlight that data, we can address that with a similar regular macro. It would look like this!

Sub ColorMe()
Dim cl As Range
Dim iCol As Variant
For Each cl In Range("$A$2:$A" & Cells(Rows.Count, "A"))
Select Case UCase(cl)
Case "A": iCol = 4
Case "B": iCol = 35
'etc for other letters. Get the numbers of the colors you need from the above macro
Case Else: iCol = xlNone
End Select
Target.Resize(1, 13).Interior.ColorIndex = iCol
Next cl
Range("A9:M"& Cells(Rows.Count,"M").End(xlUp).Row).Sort Key1:=Range("$A$9"), Order1:=xlAscending, Key2:=Range("$B$9"),Header:= _
        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 End Sub

This would go in a regular module. Open the VBE (ALT+F11) and choose Insert Module. Paste in the white panel.

HTH
lenze

 

[aegliveinterns]

I had more luck with this code, but it still had no effect unfortunately. I erased all previous modules and input the exact code you specified here. When I went to the macros menu I saw the macro titled color me, but when I pressed run it said:

Compile Argument

Named Argument Not Found

In the macro code itself, there was a yellow arrow next to the top line: Sub ColorMe()

Sorry for not getting it, again this is my first time using VBA.

 

[lenze]

Sorry!! I wrote that code on the fly without testing Here is a corrected cersion

Sub ColorMe()
Dim cl As Range
Dim iCol As Variant
For Each cl In Range("$A$9:$A" & Cells(Rows.Count, "A").End(xlUp).Row)
Select Case UCase(cl)
Case "A": iCol = 4
Case "B": iCol = 35
'etc for other letters. Get the numbers of the colors you need from the above macro
Case Else: iCol = xlNone
End Select
cl.Resize(1, 13).Interior.ColorIndex = iCol
Next cl
Range("A9:M" & Cells(Rows.Count, "M").End(xlUp).Row).Sort Key1:=Range("$A$9"), Order1:=xlAscending, Key2:=Range("$B$9"), Header:= _
        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

The change code DOES work also, Did you place it in the Sheet module? Maybe your security settings are too high. Go to Tools>MAcros>Security and make sure it's set to Medium or Low. Also, Open the VBE (ALT+F11) and then enter CTRL+G to open the Immediate Window. In the panel, type in
?Application.EnableEvents and press enter. It should retutn TRUE. IF not, enter
Application.EnableEvents = True and preess enter. Exit theVBE(ALT+Q) and now change a cell in Column "A" then macro should run.
HTH
lenzze

 

[aegliveinterns]

This is some great stuff, Lenze!

I did everything you said in the last post, but there are still a few problems:

1. It doesn't autosort by name (column b)
2. When I add new letters in column a after running the macro, nothing happens. It only affects the cells that are currently entered, not the ones I enter after pressing run. I ran the immediate events window and it said true, but it's still not working 100%
3. For some odd reason, when I press run it moves one of my "A" rows in A column into my info section at the top. The info section goes from row 1 to row 8.

Thanks again for all of your help. This is a huge help!