[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Add Conditional Format To All Cells In a Changing Range

Posted on 2014-08-25
Medium Priority
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 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 40284979

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

ID: 40305768

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

834 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