'Range' of object '_Global' failed ---runtime error 1004

[xiahuagreen]

Hello
below is my code, it works only 1/3 of the upper screen as expected, error msg comes out anywhere else. please help to identify the root cause. Thanks so much.

Sub FindMax()
'
Dim zoomCell As Range, zoomRng As Range, maxCell As Variant

ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first

If Selection.Cells.Count = 1 Then
MsgBox "You must select a range before pressing button - Try again"
End
End If

Set zoomRng = Application.Selection
Selection.Name = "zoomArea"

For Each zoomCell In Range(zoomRng.Address)
zoomCell.Cells.Interior.ColorIndex = 6 'Yellow highlight selected cells.
Next zoomCell

maxCell = WorksheetFunction.Max(Range(zoomRng.Address)) 'obtain address for each cell


With ActiveSheet.Range(zoomRng.Address)
Set c = .Find(maxCell, LookIn:=xlValues) 'find the max value cell
If Not c Is Nothing Then
maxaddress = c.Address 'Find max value in select cells.
End If
End With

ActiveWindow.Zoom = True

Range(maxaddress).Cells.Interior.ColorIndex = 22 'Red highlight for Max value. <<<<<<<<<<<<< This line is in error when debug

MsgBox "Max value = " & maxCell & " at cell " & maxaddress

ActiveWindow.Zoom = 70

maxaddress = Clear
maxCell = Clear
zoomCell = Clear
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub FindMax()
'
Dim MaxCell As Range, zoomRng As Range, maxvalue As Variant

ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first

If Selection.Cells.Count = 1 Then
    MsgBox "You must select a range before pressing button - Try again"
    Exit Sub
End If

Set zoomRng = Application.Selection
Selection.Name = "zoomArea"


zoomRng.Interior.ColorIndex = 6 'Yellow highlight selected cells.

maxvalue = WorksheetFunction.Max(zoomRng) 'obtain address for each cell


Set MaxCell = zoomRng.Find(maxvalue, LookIn:=xlValues) 'find the max value cell
If Not MaxCell Is Nothing Then
    ActiveWindow.Zoom = True
    MaxCell.Interior.ColorIndex = 22

    MsgBox "Max value = " & maxvalue & " at cell " & MaxCell.Address

    ActiveWindow.Zoom = 70
Else
    MsgBox "No Max value found"
End If
End Sub

 

[xiahuagreen]

Hi & welcome to MrExcel.
How about

Sub FindMax()
'
Dim MaxCell As Range, zoomRng As Range, maxvalue As Variant

ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first

If Selection.Cells.Count = 1 Then
    MsgBox "You must select a range before pressing button - Try again"
    Exit Sub
End If

Set zoomRng = Application.Selection
Selection.Name = "zoomArea"


zoomRng.Interior.ColorIndex = 6 'Yellow highlight selected cells.

maxvalue = WorksheetFunction.Max(zoomRng) 'obtain address for each cell


Set MaxCell = zoomRng.Find(maxvalue, LookIn:=xlValues) 'find the max value cell
If Not MaxCell Is Nothing Then
    ActiveWindow.Zoom = True
    MaxCell.Interior.ColorIndex = 22

    MsgBox "Max value = " & maxvalue & " at cell " & MaxCell.Address

    ActiveWindow.Zoom = 70
Else
    MsgBox "No Max value found"
End If
End Sub

Hi thanks for the help. I applied your routine and this time the error msg: Compile error: Ambiguous name detected: FindMax
am I getting into more trouble this time?

 

[Fluff]

You need to delete your original macro, or change the name of one of them.

 

[xiahuagreen]

Hi thanks for the help. I applied your routine and this time the error msg: Compile error: Ambiguous name detected: FindMax
am I getting into more trouble this time?

Hi, I tried this set of code and it only gave me the message "no max value found"
I am using the below set of data, hope it's not occupying too much space here...

12	13.14	14.16	15.06	15.84	16.5	17.04	17.46	17.76	17.94	18	17.94	17.76	17.46	17.04	16.5	15.84	15.06	14.16	13.14	12
12.4449	13.5949	14.6249	15.5349	16.3249	16.9949	17.5449	17.9749	18.2849	18.4749	18.5449	18.4949	18.3249	18.0349	17.6249	17.0949	16.4449	15.6749	14.7849	13.7749	12.6449
12.9984	14.1584	15.1984	16.1184	16.9184	17.5984	18.1584	18.5984	18.9184	19.1184	19.1984	19.1584	18.9984	18.7184	18.3184	17.7984	17.1584	16.3984	15.5184	14.5184	13.3984
13.6869	14.8569	15.9069	16.8369	17.6469	18.3369	18.9069	19.3569	19.6869	19.8969	19.9869	19.9569	19.8069	19.5369	19.1469	18.6369	18.0069	17.2569	16.3869	15.3969	14.2869
14.5344	15.7144	16.7744	17.7144	18.5344	19.2344	19.8144	20.2744	20.6144	20.8344	20.9344	20.9144	20.7744	20.5144	20.1344	19.6344	19.0144	18.2744	17.4144	16.4344	15.3344
15.5625	16.7525	17.8225	18.7725	19.6025	20.3125	20.9025	21.3725	21.7225	21.9525	22.0625	22.0525	21.9225	21.6725	21.3025	20.8125	20.2025	19.4725	18.6225	17.6525	16.5625
16.7904	17.9904	19.0704	20.0304	20.8704	21.5904	22.1904	22.6704	23.0304	23.2704	23.3904	23.3904	23.2704	23.0304	22.6704	22.1904	21.5904	20.8704	20.0304	19.0704	17.9904
18.2349	19.4449	20.5349	21.5049	22.3549	23.0849	23.6949	24.1849	24.5549	24.8049	24.9349	24.9449	24.8349	24.6049	24.2549	23.7849	23.1949	22.4849	21.6549	20.7049	19.6349
19.9104	21.1304	22.2304	23.2104	24.0704	24.8104	25.4304	25.9304	26.3104	26.5704	26.7104	26.7304	26.6304	26.4104	26.0704	25.6104	25.0304	24.3304	23.5104	22.5704	21.5104
21.8289	23.0589	24.1689	25.1589	26.0289	26.7789	27.4089	27.9189	28.3089	28.5789	28.7289	28.7589	28.6689	28.4589	28.1289	27.6789	27.1089	26.4189	25.6089	24.6789	23.6289
24	25.24	26.36	27.36	28.24	29	29.64	30.16	30.56	30.84	31	31.04	30.96	30.76	30.44	30	29.44	28.76	27.96	27.04	26
26.4309	27.6809	28.8109	29.8209	30.7109	31.4809	32.1309	32.6609	33.0709	33.3609	33.5309	33.5809	33.5109	33.3209	33.0109	32.5809	32.0309	31.3609	30.5709	29.6609	28.6309
29.1264	30.3864	31.5264	32.5464	33.4464	34.2264	34.8864	35.4264	35.8464	36.1464	36.3264	36.3864	36.3264	36.1464	35.8464	35.4264	34.8864	34.2264	33.4464	32.5464	31.5264
32.0889	33.3589	34.5089	35.5389	36.4489	37.2389	37.9089	38.4589	38.8889	39.1989	39.3889	39.4589	39.4089	39.2389	38.9489	38.5389	38.0089	37.3589	36.5889	35.6989	34.6889
35.3184	36.5984	37.7584	38.7984	39.7184	40.5184	41.1984	41.7584	42.1984	42.5184	42.7184	42.7984	42.7584	42.5984	42.3184	41.9184	41.3984	40.7584	39.9984	39.1184	38.1184
38.8125	40.1025	41.2725	42.3225	43.2525	44.0625	44.7525	45.3225	45.7725	46.1025	46.3125	46.4025	46.3725	46.2225	45.9525	45.5625	45.0525	44.4225	43.6725	42.8025	41.8125
42.5664	43.8664	45.0464	46.1064	47.0464	47.8664	48.5664	49.1464	49.6064	49.9464	50.1664	50.2664	50.2464	50.1064	49.8464	49.4664	48.9664	48.3464	47.6064	46.7464	45.7664
46.5729	47.8829	49.0729	50.1429	51.0929	51.9229	52.6329	53.2229	53.6929	54.0429	54.2729	54.3829	54.3729	54.2429	53.9929	53.6229	53.1329	52.5229	51.7929	50.9429	49.9729
50.8224	52.1424	53.3424	54.4224	55.3824	56.2224	56.9424	57.5424	58.0224	58.3824	58.6224	58.7424	58.7424	58.6224	58.3824	58.0224	57.5424	56.9424	56.2224	55.3824	54.4224
55.3029	56.6329	57.8429	58.9329	59.9029	60.7529	61.4829	62.0929	62.5829	62.9529	63.2029	63.3329	63.3429	63.2329	63.0029	62.6529	62.1829	61.5929	60.8829	60.0529	59.1029
60	61.34	62.56	63.66	64.64	65.5	66.24	66.86	67.36	67.74	68	68.14	68.16	68.06	67.84	67.5	67.04	66.46	65.76	64.94	64
64.8969	66.2469	67.4769	68.5869	69.5769	70.4469	71.1969	71.8269	72.3369	72.7269	72.9969	73.1469	73.1769	73.0869	72.8769	72.5469	72.0969	71.5269	70.8369	70.0269	69.0969
69.9744	71.3344	72.5744	73.6944	74.6944	75.5744	76.3344	76.9744	77.4944	77.8944	78.1744	78.3344	78.3744	78.2944	78.0944	77.7744	77.3344	76.7744	76.0944	75.2944	74.3744
75.2109	76.5809	77.8309	78.9609	79.9709	80.8609	81.6309	82.2809	82.8109	83.2209	83.5109	83.6809	83.7309	83.6609	83.4709	83.1609	82.7309	82.1809	81.5109	80.7209	79.8109
80.5824	81.9624	83.2224	84.3624	85.3824	86.2824	87.0624	87.7224	88.2624	88.6824	88.9824	89.1624	89.2224	89.1624	88.9824	88.6824	88.2624	87.7224	87.0624	86.2824	85.3824
86.0625	87.4525	88.7225	89.8725	90.9025	91.8125	92.6025	93.2725	93.8225	94.2525	94.5625	94.7525	94.8225	94.7725	94.6025	94.3125	93.9025	93.3725	92.7225	91.9525	91.0625
91.6224	93.0224	94.3024	95.4624	96.5024	97.4224	98.2224	98.9024	99.4624	99.9024	100.2224	100.4224	100.5024	100.4624	100.3024	100.0224	99.6224	99.1024	98.4624	97.7024	96.8224
97.2309	98.6409	99.9309	101.1009	102.1509	103.0809	103.8909	104.5809	105.1509	105.6009	105.9309	106.1409	106.2309	106.2009	106.0509	105.7809	105.3909	104.8809	104.2509	103.5009	102.6309
102.8544	104.2744	105.5744	106.7544	107.8144	108.7544	109.5744	110.2744	110.8544	111.3144	111.6544	111.8744	111.9744	111.9544	111.8144	111.5544	111.1744	110.6744	110.0544	109.3144	108.4544
108.4569	109.8869	111.1969	112.3869	113.4569	114.4069	115.2369	115.9469	116.5369	117.0069	117.3569	117.5869	117.6969	117.6869	117.5569	117.3069	116.9369	116.4469	115.8369	115.1069	114.2569
114	115.44	116.76	117.96	119.04	120	120.84	121.56	122.16	122.64	123	123.24	123.36	123.36	123.24	123	122.64	122.16	121.56	120.84	120
119.4429	120.8929	122.2229	123.4329	124.5229	125.4929	126.3429	127.0729	127.6829	128.1729	128.5429	128.7929	128.9229	128.9329	128.8229	128.5929	128.2429	127.7729	127.1829	126.4729	125.6429
124.7424	126.2024	127.5424	128.7624	129.8624	130.8424	131.7024	132.4424	133.0624	133.5624	133.9424	134.2024	134.3424	134.3624	134.2624	134.0424	133.7024	133.2424	132.6624	131.9624	131.1424
129.8529	131.3229	132.6729	133.9029	135.0129	136.0029	136.8729	137.6229	138.2529	138.7629	139.1529	139.4229	139.5729	139.6029	139.5129	139.3029	138.9729	138.5229	137.9529	137.2629	136.4529
134.7264	136.2064	137.5664	138.8064	139.9264	140.9264	141.8064	142.5664	143.2064	143.7264	144.1264	144.4064	144.5664	144.6064	144.5264	144.3264	144.0064	143.5664	143.0064	142.3264	141.5264
139.3125	140.8025	142.1725	143.4225	144.5525	145.5625	146.4525	147.2225	147.8725	148.4025	148.8125	149.1025	149.2725	149.3225	149.2525	149.0625	148.7525	148.3225	147.7725	147.1025	146.3125
143.5584	145.0584	146.4384	147.6984	148.8384	149.8584	150.7584	151.5384	152.1984	152.7384	153.1584	153.4584	153.6384	153.6984	153.6384	153.4584	153.1584	152.7384	152.1984	151.5384	150.7584
147.4089	148.9189	150.3089	151.5789	152.7289	153.7589	154.6689	155.4589	156.1289	156.6789	157.1089	157.4189	157.6089	157.6789	157.6289	157.4589	157.1689	156.7589	156.2289	155.5789	154.8089
150.8064	152.3264	153.7264	155.0064	156.1664	157.2064	158.1264	158.9264	159.6064	160.1664	160.6064	160.9264	161.1264	161.2064	161.1664	161.0064	160.7264	160.3264	159.8064	159.1664	158.4064
153.6909	155.2209	156.6309	157.9209	159.0909	160.1409	161.0709	161.8809	162.5709	163.1409	163.5909	163.9209	164.1309	164.2209	164.1909	164.0409	163.7709	163.3809	162.8709	162.2409	161.4909
156	157.54	158.96	160.26	161.44	162.5	163.44	164.26	164.96	165.54	166	166.34	166.56	166.66	166.64	166.5	166.24	165.86	165.36	164.74	164
157.6689	159.2189	160.6489	161.9589	163.1489	164.2189	165.1689	165.9989	166.7089	167.2989	167.7689	168.1189	168.3489	168.4589	168.4489	168.3189	168.0689	167.6989	167.2089	166.5989	165.8689
158.6304	160.1904	161.6304	162.9504	164.1504	165.2304	166.1904	167.0304	167.7504	168.3504	168.8304	169.1904	169.4304	169.5504	169.5504	169.4304	169.1904	168.8304	168.3504	167.7504	167.0304
158.8149	160.3849	161.8349	163.1649	164.3749	165.4649	166.4349	167.2849	168.0149	168.6249	169.1149	169.4849	169.7349	169.8649	169.8749	169.7649	169.5349	169.1849	168.7149	168.1249	167.4149
158.1504	159.7304	161.1904	162.5304	163.7504	164.8504	165.8304	166.6904	167.4304	168.0504	168.5504	168.9304	169.1904	169.3304	169.3504	169.2504	169.0304	168.6904	168.2304	167.6504	166.9504
156.5625	158.1525	159.6225	160.9725	162.2025	163.3125	164.3025	165.1725	165.9225	166.5525	167.0625	167.4525	167.7225	167.8725	167.9025	167.8125	167.6025	167.2725	166.8225	166.2525	165.5625
153.9744	155.5744	157.0544	158.4144	159.6544	160.7744	161.7744	162.6544	163.4144	164.0544	164.5744	164.9744	165.2544	165.4144	165.4544	165.3744	165.1744	164.8544	164.4144	163.8544	163.1744
150.3069	151.9169	153.4069	154.7769	156.0269	157.1569	158.1669	159.0569	159.8269	160.4769	161.0069	161.4169	161.7069	161.8769	161.9269	161.8569	161.6669	161.3569	160.9269	160.3769	159.7069
145.4784	147.0984	148.5984	149.9784	151.2384	152.3784	153.3984	154.2984	155.0784	155.7384	156.2784	156.6984	156.9984	157.1784	157.2384	157.1784	156.9984	156.6984	156.2784	155.7384	155.0784
139.4049	141.0349	142.5449	143.9349	145.2049	146.3549	147.3849	148.2949	149.0849	149.7549	150.3049	150.7349	151.0449	151.2349	151.3049	151.2549	151.0849	150.7949	150.3849	149.8549	149.2049
132	133.64	135.16	136.56	137.84	139	140.04	140.96	141.76	142.44	143	143.44	143.76	143.96	144.04	144	143.84	143.56	143.16	142.64	142

<colgroup><col width="62" span="21" style="width:47pt"> </colgroup><tbody>
</tbody>

 

[Fluff]

If I select the part with borders I get 168.4589 in N42 which is correct for where I have the data.
Check that your values are numbers & not text.

 

[xiahuagreen]

Hi thanks for the response. the thing that amazes me is everything works down to line row 21 or 22, as expected (the requirement is to select a zoom area and find the max value and color that cell with different color). once the zoom extends pass the row 21, 22, the program fails. is there a magical line that excel spread sheet cannot pass?

 

[JoeMo]

Fluff's code works fine for me using the data you posted, provided that I change the Find Method line to this:

Set MaxCell = zoomRng.Find(169.875, LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByRows)

The reason for using xlFormulas instead of xlValues is that if you run the macro multiple times with xlValues, after the first time it doesn't find the maxvalue (MaxCell is Nothing) for me, apparently b/c the first run causes the data to re-format to fewer decimal places. Using xlFormulas finds the correct value repeatably b/c it looks for the 'Formula" value which is what you see when you select a cell and look at the value in the formula bar.

For your full data set, placing the upperleft-most cell in A1, the max value is 169.8749 in cell $O$44.

 

[xiahuagreen]

Thank you for the explanation.
I think the search maxcell routine can find the max value, but the maxcell address routine could not record it, when all the cycles completed, the cell address has lost, ends up no way to highlight it that's why the error comes up with "range... color=22" ...
what I need to do: I can write out the max value in a remote cell, retrieve that value after the search is done, and compare that value within the zoomrng cell values, find the match and write out the address and color it.

 

[Fluff]

I think the search maxcell routine can find the max value, but the maxcell address routine could not record it

The search cannot find the Max value, that's why you get the message "No Max value found"

Based on JoeMo's suggestion try

Set MaxCell = zoomRng.Find(maxvalue, , xlFormulas, xlWhole, , , , , False)