# How to compare cells and format conditionally over a whole sheet

1. ## 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.

HilaryP

``Please Login or Register  to view this content.``

2. ## Re: How to compare cells and format conditionally over a whole sheet

oops! Thanks Fotis

3. ## Re: How to compare cells and format conditionally over a whole sheet

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.

4. ## Re: How to compare cells and format conditionally over a whole sheet

Thank you :-)
I have studied that link and I will keep trying to get it to work. Its the relative reference to update for each cell that I haven't worked out yet.
I can get it to work by row but not by cell (yet)

thanks again!

5. ## Re: How to compare cells and format conditionally over a whole sheet

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

6. ## Re: How to compare cells and format conditionally over a whole sheet

Thanks :-) here you go
you will see it works row-wise based on first cell entry but I would like it to test every cell matches.

7. ## Re: How to compare cells and format conditionally over a whole sheet

TRY

Formula:
`Please Login or Register  to view this content.`

8. ## Re: How to compare cells and format conditionally over a whole sheet

OK, so that returns TRUE if both cells in Sheet2, Col A and Sheet 2, Col B match their row equivalent on Sheet 1
so now the whole row on Sheet 2 goes green if both cells in Col A and Col B match.
... can we tweek this so that each cell in Sheet 2 goes green if it matches the cell of the same position on Sheet1 . ie test if Sheet1 A3 = Sheet2 A3, Sheet1A4 = Sheet2 A4, ...Sheet1 B5 = Sheet2 B5 etc ??

thanks again :-)

9. ## Re: How to compare cells and format conditionally over a whole sheet

Formula:
`Please Login or Register  to view this content.`

??

10. ## Re: How to compare cells and format conditionally over a whole sheet

Ah, perfect! Just the job :-)
Many thanks Fotis

11. ## Re: How to compare cells and format conditionally over a whole sheet

You are welcome and thanks for the feedback.

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.

12. ## Re: How to compare cells and format conditionally over a whole sheet

I already did the *star* and now I have also marked the thread as solved.
You saved me writing a macro so I'm very content with the outcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)