• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

compare small table with huge table and conditional formatting of cell


New Member
I am beginner with excel bare with me and help me to get solution for below

I have 2 tables- table1 and table2.

table 1 has 2 columns only col1, col2. it is the reference table for comparing values.

table 2 has 200 columns. It is data table.

I have to compare reference table(table1) with data table(table2) like -

table1 col1 & col2 compare with table2 col1

table1 col1 & col2 compare with table2 col2




table1 col1 & col2 compare with table2 col200

data value should be lesser than reference table value ie- table1 value > table2 value

if not mark cell as red color in table2.
I have checked with the Menu> Format> conditional Formatting.. but when data changes all setting gone...

so I am looking for code in macro. so that if new data comes on table2 need not not to do all settings again !!

@Devx Try the tutorials in http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ for some ideas. You can use formulas in conditional formatting to achieve this. Let me know if you are still stuck...