Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
Update column H based upon content in column B | Excel Worksheet Functions | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
how do I update a column and create new rows for new column cells | Excel Discussion (Misc queries) | |||
How do I update Column #1 based on specific value of column #2? | Excel Worksheet Functions |