Add Conditional Format To All Cells In a Changing Range

I am building an Excel workbook for uploading batch adjustments to a database. The workbook gets populated automatically from Access, with the back tab containing a row for each record in the a table. Then users copy rows from there to (among others) a "Change" tab and make their changes there. The changes are then loaded back into the table.

I want to add a macro to the Excel that will add conditional formatting to each populated cell on the Change Tab (except row 1) to the corresponding row on the original tab. Since only some rows are copied over, the comparison will not be to the same row reference, so it will be done using a Vlookup on an ID number in column A in both tabs to compare to the correct row.

So far I have this in the macro I have recorded:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=VLOOKUP(A2,SEG_D1!A:B,3,FALSE)<>B2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

This works for the 1 cell I used it on. But what I need is to have the macro do the following:

Go to Column B in the bottom row of the Change Tab. (Easy with the End Down command.)

Conditionally format that cell so that it changes to yellow if it is different from the Vlookup value on the original tab.

Move to the right one cell and repeat the process. (But the range must now change to A:C and the <> Test must be applied to cell C2!)

Keep going out to the end of the row repeating the process (with the range and tested cell changing with each move.)

Ascend to the next row up, go to column B and do it again.

Repeat for all rows above - stopping at row 2.

 The number of columns is constant in these workbooks, so it's possible to create a code set specific to each column. But the number of rows will vary, so at least part of the macro will need to deal with the issue of referring to and testing the right cell on the Change Tab against the right cell on the original tab. Once the macro has finished, all cells on the Change Tab that vary from the matching cell on the original tab should appear in yellow.

So, core questions:

1) How do I get the Macro to navigate through the cells from the bottom up (or top down, for that matter) and either stop at row 2 (if ascending) or as soon as it encounters a blank row (if descending)?

2) How do I code the Macro so that the conditional formatting tests the current cell against the Vlookup for the proper column and range?

Any suggestions appreciated.
Buck_BeasomDatabase DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

in your formula the vlookup has 2 columns A:B but you want the 3rd col as argument, I've replaced it with COLUMN(B:B)

pls try

    Range(Range("B2"), Range("B" & Rows.Count).End(xlUp).End(xlToRight)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=VLOOKUP($A2,SEG_D1!$A:B,COLUMN(B:B),FALSE)<>B2"
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Buck_BeasomDatabase DesignerAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.