How to compare cells and format conditionally over a whole sheet

I wish to compare two sheets [Sheet1 and Sheet2] within one workbook and format the second sheet conditionally, based on an exact match to the first sheet.

Cell by cell, I can add the specific conditional formatting rule to each cell but this is tedious and its hard to see where I have applied the conditional format and where not.

So I tried to construct a macro to apply to a range of cells.

I tried to assign the logical result of the compare test to a cell outside of the range[lets say on Sheet2, A1] , and then format the range cells based on that logical value. I think my error is in conditional format criterion but I am a bit stuck.

Many thanks in advance for your help!
HilaryP

    Sheets("Sheet2").Select
' conditional format criterion
    Range("A1").FormulaR1C1 = "=AND(Sheet1!R[1]C[1]= Sheet2!R[1]C[1])"
'
' format range based on logical result
    Range("A2:J4").Select               ' select range to format
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       Sheets("Sheet2").Range("A1").FormulaLocal 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3    ' Accent3 is green, Accent2 is red
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A1").Select
'
'Conditional Formatting Formulas Deletion
    Range("A1").ClearContents

Perhaps you need to re-think about Conditional Formatting. You can use it to your whole range by 1 click. Take a look to the link of how to yse CF.

How can you use Conditional Formating.

--In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.

if you'll need help on this, pls upload a small sample workbook.

TRY

Formula:

=AND($A3=Sheet1!$A3,$B3=Sheet1!$B3)

Formula:

=A3=Sheet1!A3

??

You are welcome and thanks for the feedback.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.