VBA Row Programming

**aegliveinterns** · 04-28-2010, 02:26 PM

Hello,

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

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.

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

Thank you!

**broro183** · 04-28-2010, 03:21 PM

hi aegliveinterns,

Welcome to the Forum

Can you please upload a sample file?
Are you familar with macros?
If so, can you please record a macro of your actions (relating to each question) & include this in the sample file to give us a starting point.

1) Please include an extra sheet that has the list of your Status Letters with each cell being coloured as you would like the finished product coloured.

2) This will be much clearer with your file & recorded code. However, to clarify, do you want the information sorted vertically or horizontally?
Can you please include an example "before & after" in the sample file?

Thanks
Rob

**aegliveinterns** · 04-28-2010, 06:06 PM

Hi Rob!

Thanks so much for getting back to me, this is a really important project for work and I really appreciate the help. I included a screenshot of the worksheet I'm using.

A - bright green
B - light green
C - light orange
E - yellow
F - red

I hope to sort vertically by status column, and then by name column. If possible, I'd also like to program the page to border new entries when I assign a letter to a name. And auto-sort would be awesome. Thank you so much!

Nate

**shg** · 04-28-2010, 06:30 PM

How can anyone test a macro on your PDF?

**aegliveinterns** · 04-28-2010, 08:04 PM

Sorry about that, here's the actual excel

**aegliveinterns** · 04-30-2010, 02:43 PM

Any luck with the programming?

**broro183** · 05-01-2010, 01:04 PM

hi,

You were sooo close

I think the attached file does everything you want. I made some minor changes, then moved your macro into a normal module, renamed it, and then created one line worksheet change macros in each sheet module to call your original macro. I've made the assumption that each sheet will have the same layout and have tried to make the code easier to adapt by making more variables or constants. This means that the code only needs to be changed in one place.

hth
Rob

**aegliveinterns** · 05-02-2010, 03:04 PM

Hi Rob,

Thanks for all the work you did on my worksheet! However, it didn't work when I put in a letter in the Status column. I've attached a picture of the problem. It tells me there's a compile argument, and then brings me back to the VBA screen. Please let me know what I can do on my end to fix this problem. Thanks so much!

Nate

**broro183** · 05-03-2010, 04:07 PM

Hi Nate,

Sorry, I've only just seen your post. The file works for me - let's see if we can narrow it down...?

- What is the exact wording of the error message?
- If you go to the VBE & press [alt + D + L], what line gets highlighted?
- Also in the VBE, press [alt + T + R], do any of the ticked "Available References" state "missing"?
- Is one of the ticked References called "Microsoft Excel ##.# Object Library"?
(where ##.# is "10.0" or "11.0")
If not, scroll down the list, find it & tick it.

hth
Rob