Excel 2007 Macro and VB

By boardin4life85 ·
Hi. I just received a project to work on and need lots of help. I need to create a marco using visual basic to be used in excel. I have never used VB so I am not sure what I am doing.

We have an excel book with two worksheets that are basically the same; its the information for each months billing. So there is an ID in column A and then the different options they are signed up for in columns like B-N.

What I need to do is have a macro that first checks the ID in sheet 2 and checks for it in sheet 1. If it finds it in sheet 1, then it needs to compare the rest of the values in that row and see if there is differences, and needs to highlite them. Or if there is no change, then it can just leave it the way it is.
But if a new ID is added in the current month (sheet 2) then it will not be in sheet 1 (previous month). So in that case it would have to highlite the whole row to knot its a new ID and all billing options are new

I hope this description makes sense...but if you can help me out that would be awesome. Like I said, I have not used VB before so this is a complicated task for me. Thanks for your help

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

i start by recording the macro, then viewing the code it generates

by sgt_shultz In reply to Excel 2007 Macro and VB

check out Excel help 'record macro' for how to do this.
check out microsoft vb tech support, for example Sesame Scripts
be prepared to spend some time climbing the learning curve

Collapse -

Excel VBA

by chaos.theory In reply to Excel 2007 Macro and VB


If you'd like to email your workbook, I'll take a look and
create some code to do this.



Collapse -

Use Macro Recorder and read a book

by rob In reply to Excel 2007 Macro and VB

As someone else said - the handiest tool is the macro recorder function in Excel, then edit the macro to see how it is scripted.

But you realy should get hold of a good book on the subject, e.g. Wrox Excel (version) VBA Prgrammers Reference are quite good - provided you understand coding basics that is.

Collapse -

A place to begin

by mishanv In reply to Excel 2007 Macro and VB

Pick up a copy of Master Visually for Excel VBA Programming, I got it at Borders. It is laid out in a reference format, excellent tables of contents with plenty of screen shots and focussed examples.

About a third of my work is now making/changing Excel forms and this book is constantly used.

Collapse -


by mark.xm.sworn In reply to A place to begin

I had to produce something similar to this, but without using any VBA coding (against company policy)... I achieved this through a combination of conditional formatting and VLOOKUPS.

A bit hungry on memory, but as reliable as VBA, with the right conditions.

Collapse -

comparing data for each ID

by venkat1926 In reply to Excel 2007 Macro and VB

in both sheets the row no. 1 is headings
in sheet1(which I called "last") and sheet 2(which I called "current") the ID is in column from A2 down
the data (either text or number are in columns B to N. sheet 2 has an additional ID which is not in sheet1 and corresponding data in columns B to N
(I have excel 2002(windows xp). I think this will work in excel 2007
if there is problem in administering the macro send an email to me I shall send the sample workbook ( this macro was run;.
try this macro (modify to suit you)
Option Base 1
Sub test()

Dim i As Integer
Dim clast(13)
Dim ccurrent(13)
Dim x
Dim y(13)
Dim z(13)
Dim cfind As Range
Dim rng As Range, c As Range
With Worksheets("current")
Set rng = Range(.Range("a2"), .Range("a2").End(xlDown))
For Each c In rng
x = c.Value
For i = 1 To 13
Set ccurrent(i) = c.Offset(0, i)

y(i) = ccurrent(i).Value
Next i
With Worksheets("last").Columns("A:A")
On Error Resume Next
Set cfind = .Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
c.EntireRow.Interior.ColorIndex = 6
GoTo line1
End If
For i = 1 To 13
Set clast(i) = cfind.Offset(0, i)
z(i) = clast(i).Value
If z(i) = y(i) Then
GoTo line1
ccurrent(i).Interior.ColorIndex = 3
End If
Next i
End With
Next c
End With
End Sub

Collapse -

Send me example

by chaos.theory In reply to Excel 2007 Macro and VB

As I indicated in my other reply, if you'd like to send me
your workbook I'll take a look and put some coding
together for you.

I'll also let you have some narrative as to what and how
the code works to help you get started on understanding



Related Discussions

Related Forums