Microsoft Excel - Macro for compare 2 range in one Excel sheet

Asked By John on 08-Nov-10 04:59 AM

I have an Excel file given as below. I want to compare Two Range in one excel sheet.Range1 is Total Panel (Blue Highlighted) Rage2 is Yellow Highlighted. I want which panel nos. missing in range 1. Missing panels should be highlighted with any other colour.Can you any one give a solution for this. I got a macro for this but not working properly. Can anyone to help to make it work properly.

' If the cells don't match, color cell2.
If no_match Then
With cell2.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Else
With cell2.Interior
.ColorIndex = xlNone
End With
End If
Next cell2
End Sub

ZONE -1 BLOCK -1

ZONE -1 BLOCK - 2

ZONE -1 BLOCK - 13

ZONE -1 BLOCK - 14

ZONE -1 BLOCK - 15

ZONE -1 BLOCK - 16

ZONE -1 BLOCK - 17

ZONE -1 BLOCK - 18

TOTAL

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

SL.NO

PANEL NO.

REQD

PANEL NO.

REQD

QTY

QTY

QTY

QTY

QTY

QTY

QTY

QTY

QTY

1

IP-01

44

1

IP-01

42

1

IP-01

57

1

IP-01

25

1

IP-01

74

1

IP-01

71

1

IP-01

54

1

IP-01

71

IP-01

438

2

IP-03

4

2

IP-D-160

1

2

IP-05

1

2

IP-02

50

2

IP-03

11

2

IP-01A

1

2

IP-03

29

2

IP-02

2

IP-01A

1

3

IP-D-160

2

3

IP-D-180

4

3

IP-D-160

3

3

IP-03

16

3

IP-0-52

1

3

IP-03

11

3

IP-D-165

2

3

IP-03

20

IP-03

91

4

IP-D-180

3

4

IP-D-180S

6

4

IP-D-180

4

4

IP-0-58

4

4

IP-0-58

1

4

IP-0-58

1

4

IP-D-180

11

4

IP-05

2

IP-D-160

7

5

IP-D-180S

5

5

IP-0-59

4

5

IP-D-180S

4

5

IP-0-60

1

5

IP-0-62

1

5

IP-0-60

2

5

IP-D-180S

2

5

IP-D-160

1

IP-D-162

8

6

IP-0-66

2

6

IP-0-64

1

6

IP-0-60

5

6

IP-0-62

1

6

IP-0-70

4

6

IP-0-68

3

6

IP-0-58

2

6

IP-D-180

9

IP-D-165

2

7

IP-0-72

1

7

IP-0-68

1

7

IP-0-66

1

7

IP-0-66

2

7

IP-0-78

4

7

IP-0-72

1

7

IP-0-64

2

7

IP-D-180S

7

IP-D-180

49

8

IP-0-84

1

8

IP-0-82

1

8

IP-0-82

1

8

IP-0-68

2

8

IP-0-82

3

8

IP-0-76

1

8

IP-0-70

1

8

IP-0-58

2

IP-D-180S

50

9

IP-0-86

1

9

IP-0-95

1

9

IP-0-86

1

9

IP-0-72

1

9

IP-0-86

1

9

IP-0-80

3

9

IP-0-74

1

9

IP-0-60

1

IP-0-58

10

10

IP-0-90

2

10

IP-1-02

1

10

IP-1-00

1

10

IP-0-74

1

10

IP-0-90

1

10

IP-0-84

2

10

IP-0-78

2

10

IP-0-66

1

IP-0-59

4

11

IP-1-08

1

11

IP-1-23

2

11

IP-1-02

1

11

IP-0-76

1

11

IP-0-96

2

11

IP-0-98

3

11

IP-0-80

1

11

IP-0-70

1

IP-0-60

9

12

IP-1-24

1

12

IP-2-80

1

12

IP-1-28

1

12

IP-0-86

1

12

IP-2-30

1

12

IP-2-94

2

12

IP-0-86

2

12

IP-0-72

1

IP-0-64

3

13

IP-1-40

1

13

IP-2-96

2

13

IP-2-82

2

13

IP-0-88

1

13

IP-2-82

2

13

IP-3-24

2

13

IP-0-96

1

13

IP-0-74

1

IP-0-66

6

14

IP-2-76

1

14

IP-3-01

2

14

IP-2-92

2

14

IP-0-94

1

14

IP-3-20

1

14

IP-3-40

2

14

IP-0-98

3

14

IP-0-76

1

IP-0-68

6

15

IP-2-90

2

15

IP-3-23

2

15

IP-2-96

3

15

IP-1-42

1

15

IP-3-42

1

15

IP-3-48

2

15

IP-2-82

1

15

IP-0-78

1

IP-0-70

6

16

IP-2-92

2

16

IP-3-37

2

16

IP-3-04

2

16

IP-2-12

1

16

IP-3-44

1

16

IP-3-62

1

16

IP-2-98

2

16

IP-0-80

3

IP-0-72

4

17

IP-2-96

4

17

IP-3-66

1

17

IP-3-30

3

17

IP-2-40

1

17

IP-3-66

1

17

IP-3-76

1

17

IP-3-10

1

17

IP-0-82

2

IP-0-74

3

18

IP-2-98

2

18

IP-3-72

3

18

IP-3-38

2

18

IP-2-98

2

18

IP-3-70

1

18

IP-3-78

6

18

IP-3-26

1

18

IP-0-84

1

IP-0-76

3

19

IP-3-48

1

19

IP-3-78

2

19

IP-3-52

2

19

IP-3-96

3

19

IP-3-78

1

19

IP-3-98

2

19

IP-3-30

1

19

IP-0-94

1

IP-0-78

7

20

IP-3-50

1

20

IP-3-85

2

20

IP-3-62

1

20

IP-4-08

1

20

IP-3-80

6

20

IP-4-04

2

20

IP-3-34

2

20

IP-0-98

3

IP-0-80

7

21

IP-3-52

1

21

IP-3-89

2

21

IP-3-78

3

21

IP-4-12

1

21

IP-3-86

1

21

IP-4-06

1

21

IP-3-36

1

21

IP-1-08

1

IP-0-82

7

22

IP-3-64

1

22

IP-4-07

2

22

IP-3-84

1

22

IP-4-52

1

22

IP-3-90

2

22

IP-4-08

1

22

IP-3-38

2

22

IP-2-76

1

IP-0-84

4

23

IP-3-78

1

23

IP-4-45

1

23

IP-4-02

2

23

IP-4-54

1

23

IP-3-92

1

23

IP-4-10

1

23

IP-3-52

1

23

IP-2-88

1

IP-0-86

6

24

IP-3-80

2

24

IP-4-74

1

24

IP-4-26

1

24

IP-4-64

1

24

IP-4-00

2

24

IP-4-12

1

24

IP-3-54

1

24

IP-2-94

2

IP-0-90

3

25

IP-3-96

1

25

IP-4-87

1

25

IP-5-00

1

25

IP-4-66

1

25

IP-4-08

1

25

IP-4-14

1

25

IP-3-56

1

25

IP-2-96

1

IP-0-95

1

26

IP-4-04

1

26

IP-5-24

1

26

IP-5-06

1

26

IP-4-68

1

26

IP-4-30

3

26

IP-4-18

4

26

IP-3-58

1

26

IP-3-00

1

IP-0-96

3

27

IP-4-18

2

27

IP-5-47

2

27

IP-5-18

1

27

IP-4-72

2

27

IP-4-32

3

27

IP-4-20

1

27

IP-3-82

1

27

IP-3-26

2

IP-0-98

9

28

IP-4-22

1

28

IP-5-38

1

28

IP-5-38

1

28

IP-4-76

2

28

IP-4-40

1

28

IP-4-22

1

28

IP-3-88

1

28

IP-3-28

1

IP-1-02

2

29

IP-4-36

1

29

IP-5-66

2

29

IP-5-44

1

29

IP-4-90

1

29

IP-4-50

2

29

IP-4-26

3

29

IP-3-94

1

29

IP-3-36

1

IP-1-08

2

30

IP-4-52

1

30

IP-5-69

10

30

IP-5-58

2

30

IP-4-92

2

30

IP-4-54

1

30

IP-4-28

1

30

IP-4-08

1

30

IP-3-44

1

IP-1-23

2

31

IP-4-70

3

31

IP-5-86

1

31

IP-5-54

1

31

IP-4-96

1

31

IP-4-62

1

31

IP-4-38

1

31

IP-4-38

1

31

IP-3-46

1

IP-1-24

1

32

IP-4-74

4

32

IP-5-89

2

32

IP-5-60

1

32

IP-5-14

2

32

IP-4-64

1

32

IP-4-58

1

32

IP-4-60

1

32

IP-3-52

1

IP-1-40

1

33

IP-5-44

1

107

33

IP-5-64

2

33

IP-5-16

1

33

IP-4-88

1

33

IP-4-84

1

33

IP-4-66

2

33

IP-3-58

1

IP-1-42

1

34

IP-5-50

2

34

IP-5-68

3

34

IP-5-24

1

34

IP-5-40

1

34

IP-5-00

1

34

IP-4-74

1

34

IP-3-70

1

IP-2-76

2

35

IP-5-60

1

35

IP-5-70

8

35

IP-5-26

2

35

IP-5-58

2

35

IP-5-02

1

35

IP-5-14

1

35

IP-3-72

1

IP-2-80

1

36

IP-5-66

1

36

IP-5-76

1

36

IP-5-28

2

36

IP-5-84

1

36

IP-5-22

1

36

IP-5-56

3

36

IP-4-04

1

IP-2-82

5

37

IP-5-68

1

TOTAL

127

37

IP-5-32

1

37

IP-5-62

2

37

IP-5-24

1

37

IP-5-60

1

37

IP-4-14

1

IP-2-90

2

38

IP-5-68A

1

38

IP-5-34

2

38

IP-5-60

1

38

IP-5-46

1

38

IP-5-62

1

38

IP-4-22

1

IP-2-92

4

39

IP-5-70

1

39

IP-5-48

1

39

IP-5-68

3

39

IP-5-52

1

39

IP-5-64

4

39

IP-4-34

3

IP-2-94

4

40

IP-5-72

1

40

IP-5-50

1

40

IP-5-78

2

40

IP-5-54

2

40

IP-5-66

2

40

IP-4-38

4

IP-2-96

10

41

IP-5-74

2

41

IP-5-56

1

41

IP-5-82

1

41

IP-5-58

1

41

IP-5-68

1

41

IP-4-44

1

IP-2-98

6

42

IP-5-82

1

42

IP-5-60

5

42

IP-5-48

1

42

IP-5-62

2

42

IP-5-70

6

42

IP-4-82

1

IP-3-00

1

43

IP-5-90

1

43

IP-5-64

2

43

IP-5-56

1

43

IP-5-64

2

43

IP-5-72

1

43

IP-5-18

1

IP-3-01

2

44

IP-5-98

1

44

IP-5-66

3

44

IP-5-66

7

44

IP-5-66

2

44

IP-5-74

1

44

IP-5-26

1

IP-3-10

1

114

45

IP-5-68

1

45

IP-5-30

1

45

IP-5-68

2

45

IP-5-78

4

45

IP-5-34

2

IP-3-23

2

46

IP-5-70

9

46

IP-5-14

1

46

IP-5-70

5

46

IP-5-86

1

46

IP-5-38

1

IP-3-24

2

114

47

IP-5-74

3

47

IP-5-92

1

47

IP-5-72

3

47

IP-5-92

3

47

IP-5-44

1

IP-3-26

3

107

48

IP-5-78

1

48

IP-5-76

2

48

IP-5-74

3

TOTAL

166

48

IP-5-48

1

IP-3-30

4

127

49

IP-5-84

1

49

IP-5-86

1

49

IP-5-78

1

49

IP-5-52

1

IP-3-34

2

188

50

IP-5-86

2

50

IP-5-70

4

50

IP-5-84

1

50

IP-5-54

1

IP-3-36

2

194

51

IP-D-162

3

51

IP-5-88

2

51

IP-5-92

2

51

IP-5-56

1

IP-3-37

2

187

52

IP-D-180

6

52

IP-5-34

1

52

IP-5-96

1

52

IP-5-60

1

IP-3-38

4

166

53

IP-D-180S

8

53

IP-5-16

1

53

IP-D-162

2

53

IP-5-64

3

IP-3-40

2

194

TOTAL

188

54

IP-5-74

1

54

IP-D-180

5

54

IP-5-66

2

IP-3-48

3

55

IP-6-14

1

55

IP-D-180S

10

55

IP-5-68

6

IP-3-50

1

###

56

IP-D-162

3

TOTAL

187

56

IP-5-70

3

IP-3-52

5

57

IP-D-180

7

57

IP-5-72

1

IP-3-54

1

58

IP-D-180S

8

58

IP-5-76

3

IP-3-56

1

TOTAL

194

59

IP-5-78

2

IP-3-58

2

60

IP-5-82

1

IP-3-62

2

1277

61

IP-5-92

2

IP-3-64

1

1130

TOTAL

194

IP-3-66

2

147

IP-3-72

4

IP-3-76

1

IP-3-78

13

IP-3-80

8

IP-3-82

1

IP-3-85

2

IP-3-88

1

IP-3-89

2

IP-3-94

1

IP-3-96

4

IP-4-04

4

IP-4-06

1

IP-4-07

2

IP-4-08

4

IP-4-10

1

IP-4-12

2

IP-4-14

2

IP-4-18

6

IP-4-20

1

IP-4-22

3

IP-4-26

4

IP-4-28

1

IP-4-36

1

IP-4-38

6

IP-4-45

1

IP-4-52

2

IP-4-58

1

IP-4-60

1

IP-4-66

3

IP-4-70

3

IP-4-74

6

IP-4-84

1

IP-4-87

1

IP-5-00

2

IP-5-02

1

IP-5-14

4

IP-5-22

1

IP-5-24

3

IP-5-38

3

IP-5-44

3

IP-5-46

1

IP-5-47

2

IP-5-50

3

IP-5-52

2

IP-5-54

4

IP-5-56

6

IP-5-58

5

IP-5-60

10

IP-5-62

5

IP-5-64

13

IP-5-66

19

IP-5-68

17

IP-5-68A

1

IP-5-69

10

IP-5-70

36

IP-5-72

6

IP-5-74

10

IP-5-78

10

IP-5-82

3

IP-5-84

3

IP-5-86

5

IP-5-89

2

IP-5-90

1

IP-5-92

8

IP-5-96

1

IP-5-98

1

TOTAL

1130

Rolf Jaeger replied to John on 08-Nov-10 12:24 PM

Hi John:

your post seems to have been clipped somewhere in the process (the Total panel did not show at all). Please repost with just a subset of your worksheet (or alternatively upload the workbook).

Also, please explain how you would like the results of the comparison be presented to you.

This is my worksheet. My range 2 is Column A to columns P. (A1,A2 to P1,P2 contains the columns header. Range1 starting from Column T1 and Column U1.
(T1,T2, U1, U2 are column Header). Is it possible to upload my excel sheet anyway here. I don't know how to upload excel file here.

I want to check range2 for which panels are not added in range1. Some panels No. are missing in range1. eg. IP-5-18 not in range1 but it is on range2. I want to add these panels to range1. It is very difficult to find out which panels are not added yet in Range1. can you give a macro can that can findout which panel need to add range1 it will be very easy for me.

Actually I need to add which panels are missing in range1 from range2 as a sorting order. I mean serial wise on the same sheet or another sheet one.

John replied to Rolf Jaeger on 14-Nov-10 05:58 AM

I added my Excel file here and waiting for your favourable reply

Rolf Jaeger replied to John on 15-Nov-10 08:34 PM

Hi John:

Please give the code listed below a try. It will list all panels appearing in your range2, but missing in your range1 in column W of your worksheet.

JOHNS FILE.zip If possible can you please update the macro suitable for this attached excel file.

Rolf Jaeger replied to John on 20-Nov-10 01:59 PM

Hi John:

sorry can't do, for a couple reasons:

your latest Excel workbook doesn't have much in common with the workbook you described in your original post (quite frankly I am not clear at all what you are trying to accomplish with the macro I originally proposed in this latest workbook)

I am trying to help people develop their own VBA coding capabilities. When I provide extensive code I am doing so in the hope that the people I am helping begin to pick up their end and try to modify my code to their specific requirements (at least within reason), and

your final request is bordering a request for contract work; if that's what you had in mind send me e-mail at office@soarentcomputing.com.

I trust you understand my position.

Best wishes,

Rolf

John replied to Rolf Jaeger on 21-Nov-10 07:47 AM

Sir

I need to check only the PANEL NO. not only look in odd columns. That correction only I just ask you. I don't have enough knowledge of macro or VBA. But I would like to learn.