Add Conditional Format To All Cells In a Changing Range

Posted on 2014-08-25
Last Modified: 2014-09-05
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:= _
    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.
Question by:Buck_Beasom
    LVL 47

    Accepted Solution


    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:= _
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
    The canonical version of this article is on my web site here: A companion presentation is available here:
    Viewers will learn how to find and create templates in Excel 2013.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now