sheet 1 have 400 columns and data row upto 10000 rows, i want to compare with sheet 2 it has 600 columns means with additional columns in different order. Compare sheet1 columns data with sheet2( as different column order) and highlight difference in sheet 2 and put difference in sheet 3 with highlighting the cell for mismatch, im new to VBA need your support,
you can highlight any one column as a primary column in sheet 1
Instead of hoping someone will write your code or teach you from scratch, you might have better luck with putting some research into how to do this with Excel's built-in tools. Therefore, my answer is "don't use VBA" — organizing data is what Excel is meant for and there is plenty of built-in functionality that you're probably not aware of.
First, there's Spreadsheet Compare (available in certain versions), which compares two workbooks (or two versions of the same workbook) and helps you see and organize differences between them. You can also identify potential issues, such as changes in formulas or calculations, or manually entered values.
Also built-in, see Consolidate data in multiple worksheets, allowing you to summarize and report results from separate worksheets into a single document. The sheets can be in the same workbook, or in other workbooks. When you consolidate data, you assemble data so that you can more easily update and aggregate as necessary.
If the columns are in different orders between the worksheets and that causes an issue for you (or for the built-in tools) is a problem, a simple fix is to simply sort the columns alphabetically on both sheets. In order to do this you just need to choose Left-to-Right in the Sort Options.
I'm sure there are other relevant features I'm not thinking of; take a look though "all" the commands available on your version's ribbon to see if there are other commands you're not aware of.
There are also a number of worksheet functions that could also help with a process like this. Exactly which ones depends on your needs (impossible for others to advise on without knowing the details of your current organization method).
The Insert Function Dialog
Off the top of my head, VLOOKUP, HLOOKUP, INDEX, MATCH, FIND, MID, LEFT and RIGHT could all potentially be beneficial to this task. (And still, no VBA required.) Find out more on those at the official Categorized List of Excel Functions, and also see the Lookup Functions section specifically.
Finally, there are a number of free or paid third-party add-ins specifically for Comparing & consolidating worksheets. For example, here is a lengthy list of the comparison functionality offered by DiffEngineX.
It's very common for Excel users to have a task at hand and assume that it's necessary to dive into VBA, without realizing Excel already provides the functionality they need. As a rule of thumb, ask yourself "Is this unique, or is this something that someone might have needed to do before?" If its not unique, chances are it's either already built-in, or there's an existing solution somewhere online.
