Compare a selected Range with a Named range and select cells that do not exist
 

Looks like the CF issue with trying to select a group of cells based on CF
shadings isn't going to happen.

So maybe I'll try a different avenue.

I'm using range A2:A27.
Cell C29 is populated by a ListBox.
The value in C29 will correspond to a Named cell range.

I'd like to find a way to compare each cell in the range to each cell in the
Name range. Range A2:A27 will have all the items that are in the defined
Name range. However, the defined Name range will not necessarily have all
the items that are in A2:A27. Whichever items were missing, I need to have
them selected.

Would this be done easier?

Thanks,
Paul

Compare a selected Range with a Named range and select cells that do not exist
 

Assume everything is on Sheet3

Dim rng as Range, rng1 as Range, cell as Range
Dim res as Variant
with Worksheets("Sheet3")
set rng = .Range(.OleObjects("Listbox1").Object.value)
set rng1 = nothing

for each cell in .Range("A2:A27")
res = Application.Match(cell.Value,rng,0)
if iserror(res) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.Select
End if


--
Regards,
Tom Ogilvy


"PCLIVE" wrote in message
...
Looks like the CF issue with trying to select a group of cells based on CF
shadings isn't going to happen.

So maybe I'll try a different avenue.

I'm using range A2:A27.
Cell C29 is populated by a ListBox.
The value in C29 will correspond to a Named cell range.

I'd like to find a way to compare each cell in the range to each cell in
the
Name range. Range A2:A27 will have all the items that are in the defined
Name range. However, the defined Name range will not necessarily have all
the items that are in A2:A27. Whichever items were missing, I need to
have
them selected.

Would this be done easier?

Thanks,
Paul