VBA or Macro to Update Column
 

Excel 2000

Does anyone have some code that would go through a range of cells on
worksheet A and then search a range of cells in worksheet B and if it
found a match (these would be matching dates) it would change the cell
colors of that cell's column and update a different cell in the column
with text from worksheet A.

e.g.
Worksheet A has the following data
A1 - Birthday A2 - 1/6/04

In worksheet B when the macro is run (on a button trigger) it would
look for the date 1/6/04 in row 2. When it is found, let's say it is
found at cell P2, it would first change the fill color to yellow for
column P and then put the text Birthday in cell P5.

I wouldn't mind working it out, but I'm having trouble finding a good
reference. The http://support.microsoft.com/support...01/default.asp
document is no longer available.

Thanks,

Carolyn

VBA or Macro to Update Column
 

Sub MarkDate()
Dim dt As Long
Dim desc As String
Dim rng As Range, rng1 As Range, res As Variant
Dim cell As Range
Set rng = Worksheets("WorksheetA").Range("A2")
dt = rng.Value
desc = rng.Offset(-1, 0).Value
Set rng1 = Worksheets("WorksheetB").Rows(2).Cells
res = Application.Match(dt, rng1, 0)
If Not IsError(res) Then
Set cell = rng1(1, res)
cell.EntireColumn.Interior.ColorIndex = 6
cell.Offset(3, 0).Value = desc
Else
MsgBox rng.Text & " not found"
End If
End Sub

http://msdn.microsoft.com/office/und.../odc_super.asp

URL should all be on one line (you can fix it in notepad). Then paste it
into your browser navigation Textbox.

--
Regards,
Tom Ogilvy



"Carolyn" wrote in message
om...
Excel 2000

Does anyone have some code that would go through a range of cells on
worksheet A and then search a range of cells in worksheet B and if it
found a match (these would be matching dates) it would change the cell
colors of that cell's column and update a different cell in the column
with text from worksheet A.

e.g.
Worksheet A has the following data
A1 - Birthday A2 - 1/6/04

In worksheet B when the macro is run (on a button trigger) it would
look for the date 1/6/04 in row 2. When it is found, let's say it is
found at cell P2, it would first change the fill color to yellow for
column P and then put the text Birthday in cell P5.

I wouldn't mind working it out, but I'm having trouble finding a good
reference. The
http://support.microsoft.com/support...01/default.asp
document is no longer available.

Thanks,

Carolyn