Make 2 sets of code work together.

[wcmash]

How can I make the following code work together?
Thanks

Sub Range()
Dim Values As Range
Set Values = Sheets("Fahey Procedure List").Range("c1:c4000")
ActiveWorkbook.Names.Add Name:="Values", RefersTo:=Values
End Sub



Sub Highlight_Duplicates(Values As Range)

Static blnColor As Boolean
'
' Make sure there's no highlighting already in our range:
Values.Interior.ColorIndex = xlColorIndexNone

For Each Cell In Values.Cells
'
' If we're on an un-highlighted cell...
If Cell.Interior.ColorIndex = xlColorIndexNone Then
'
' ...see if it is unique or if it has any duplicates:
If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
'
' Toggle between the two colors:
If blnColor Then
Application.ReplaceFormat.Interior.ColorIndex = 6
Else
Application.ReplaceFormat.Interior.ColorIndex = 7
End If
'
' Highlight all duplicate cells:
Cells.Replace What:=Cell.Value, Replacement:=Cell.Value, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

blnColor = Not (blnColor)

End If
Else
'
' ...Else we're on a cell that has already been highlighted;
' we already know it's a duplicate so do nothing else to it.

End If

Next Cell

End Sub

 

[SkipVought]

Hi,

FIRST off, Range is a protected word. Use something else for your subroutine name.

Other than that, what is NOT working?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wcmash]

Ok not to sound stupid but I had someone on here help me with the Highlight_Duplicates code and I have been trying to figure out how to make it run. The Sub range is what i came up with after a litle research on the net. Im not a VB person was just asked to figure out how to do it. If you can help that would be great.

 

[Gruuuu]

wcmash, Skip is saying that you can't have a sub named "Range" First try to change that (and anything that refers to it) to something else, preferable something that isn't already taken.
Furthermore I would change the variable "Values" to something else also.

Now, unless I am mistaken, you wish to 1) define a named range using VBA which you will then 2) Highlight the duplicates inside that named range?

How you proceed will depend largely on how you want the user to kick if off. Do you want them to select a range & click a button? Do you want them to open the macro list and run it from there? (not recommended) Do you want the script to run before the file is saved, so the highlights are included?

 

[SkipVought]

CHANGE the name of your subroutine to ...

Sub SetNamedRange()
    Dim Values As Range
    Set Values = Sheets("Fahey Procedure List").Range("c1:c4000")
    ActiveWorkbook.Names.Add Name:="Values", RefersTo:=Values
End Sub

This you run ONE TIME unless your range changes.

HOW do you want to run this highlight routine? I ran it like this ane it works just fine...

Sub test()
    Highlight_Duplicates [Values]
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wcmash]

I would like it to run from a macro list. and i have tried adding the code to a module and can't get it to show up on the macro list.

 

[fumei]

The point being is that your procedure Highlight_Duplicates is looking for a parameter to be passed to it - Value as Range.

Sub Highlight_Duplicates(Values As Range)

Therefore you Call it with that parameter.

Thus, Skip's:

    Highlight_Duplicates (Values)

I completely agree with the comments about names. Do not use "Range" nor "Values".

Sub Range()
Dim Values As Range
Set Values = Sheets("Fahey Procedure List").Range("c1:c4000")
ActiveWorkbook.Names.Add Name:="Values", RefersTo:=Values
Highlight_Duplicates (Values)
End Sub

Is: Set Values = Sheets("Fahey Procedure List").Range("c1:c4000")

correct syntax?


unknown