Is this possible with VBA?

[gayjaybird]

Here's a screenshot of my worksheet in Excel 2007:

What I would like to do, is highlight the row in the table for the current week, and then match the names from that row to the phone list. Can anyone help?

 

[Damon Ostrander]

Hi gayjaybird,

Yes, it can be done with VBA. But you didn't mention what functionality you want. For example, do you want a macro that when you run it will pop up a message box with the phone number of the On Call person? Or perhaps a function that you can call from a worksheet cell that will return the phone numbers for one or more of the persons on the current week's line?

Damon

 

[facethegod]

Try this in the VBE. Press Alt + F11->Insert/Module, then copy and paste. Finally, within the module press F5 to run

Option Explicit
Sub try()
Dim se As Variant
Dim i As Double
Dim c, cell As Range
i = 6
se = Int(Now()) + 1 - Application.Weekday(Int(Now()), 2) Mod 7
Set c = Range("A:A").Find(se, LookIn:=xlValues, MatchCase:=True)
If c Is Nothing Then Exit Sub
Range(c.Address).Interior.ColorIndex = 5
For Each cell In Range(c.Address).Offset(0, 1).Resize(1, 5)
i = i + cell.Count
Cells(i, "K") = cell
Cells(i, "L") = Application.Index(Range("J7:J65536"), Application.Match(cell, Range("I7:I65536"), 0), 1)
Next
End Sub

HTH


HTH

 

[gayjaybird]

Ok. I'm still a little new to VBA . What I was hoping to do was that when I run the macro, the cells for the current week would format like I've got the date--light green background with a dark green text, and then format the matching names in the phone list. Colors are unimportant as long as it's readable; this is just so that I (or another user) can tell at a glance who is on call and what their phone numbers are. If there is a way to write this so that I could get a list I can paste into an e-mail for distribution that would be great as well.

 

[Norie]

Why not use VLOOKUP to pull the phone numbers from the list?

Then use conditional formatting to highlight person(s) and phone number(s) for that week.

 

[gayjaybird]

I'm not sure how to make that happen in Excel 2007. With the new formatting wizard, I can get the date to highlight, but nothing else.

 

[Norie]

As far as I'm aware there's no real change in the functionality of conditional formatting in 2007.

Isn't it just that extra features have been added?

If you can get the date highlighted it shouldn't be too hard to do the row.

Just make sure you select the right range and use the right formula.

 

[gayjaybird]

It's writing the formula that's confusing me. I got the date to work through the formatting wizard. Also, this has to be compatible for XL 2003 users, and I'm afraid we'd need more than 3 conditions. One thing I'm noticing about conditional formats is that in 2003 you could make a rule to the effect of "if column A contains a date within the current week, apply formatting to A:G in that row." Using the Wizard in 2007, the formula says to highlight cells that contain a date within the current week. Since B-G contain text and not dates, they don't highlight even if I select the whole table.

FTG, I tried pasting that code into the editor, but it doesn't seem to do anything. What am I doing wrong?

 

[Norie]

Conditional formatting is not restricted to 3 conditons prior to 2003, the limit is 3 formats, so I'm not quite sure what you mean.

 

[daniels012]

can you not use conditional formatting?
Entering the formula in the cell you have highlighted...
You could use Conditional Formatting "FormulaIs" and make the formula something like:
=AND($A9<=TODAY(),A9>=TODAY()-7)
You can use "Format Painter" to copy your formula into all the cells

The second part will be a bit trickier. I'll have to work on this a bit.

Michael