Add Conditional Format To All Cells In a Changing Range
Posted on 2014-08-25
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:= _
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
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.