VBA - Highlight and format the duplicate value found in a column of data

[Kerryx]

When the code runs it asks the user to input the range to check in this example $I$1:$I$21

I have manually entered in the countif formula the "i" value so it reads COUNTIF(i$1:i1,i1)

IS there a way for the formula to take the value form the input $I$1:$I$21 and use it in my countif formula or will i need to use a second input?

The code is not pretty but i am doing my best, sorry if it offends the pro's out there

Dim rng As Range
Dim cf As FormatCondition
On Error Resume Next
Set rng = Application.InputBox(prompt:="Enter the range to check", Title:="Cell Reference", Type:=8)
On Error GoTo 0
'Check for Cancel button
If rng Is Nothing Then Exit Sub
Set rng = rng
Set cf = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=COUNTIF(i$1:i1,i1)>1")
cf.Interior.Color = vbRed
cf.NumberFormat = "0.00"

this is what i am trying to accomplish

 

[mse330]

Hi ... Try the below code

Sub Test()

Dim rng As Range, cf As FormatCondition
On Error Resume Next
Set rng = Application.InputBox(prompt:="Enter the range to check", Title:="Cell Reference", Type:=8)
'On Error GoTo 0
'Check for Cancel button
If rng Is Nothing Then Exit Sub
Set rng = rng
Set cf = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=COUNTIF(" & rng.Address & "," & rng.Cells(1).Address(0, 0) & ")>1")
cf.Interior.Color = vbRed
cf.NumberFormat = "0.00"

End Sub

 

[Kerryx]

Hi thanks for this almost right, in the countif formula it is formatting all instances of the duplicate values instead of the second instance of the value.

 

[mse330]

Ok, try now

Sub Test()

Dim rng As Range, cf As FormatCondition
On Error Resume Next
Set rng = Application.InputBox(prompt:="Enter the range to check", Title:="Cell Reference", Type:=8)
'On Error GoTo 0
'Check for Cancel button
If rng Is Nothing Then Exit Sub
Set rng = rng
With rng
  Set cf = .FormatConditions.Add(Type:=xlExpression, Formula1:= _
    "=COUNTIF(" & .Cells(1).Address & ":" & .Cells(1).Address(0, 0) & "," & .Cells(1).Address(0, 0) & ")>1")
End With
cf.Interior.Color = vbRed
cf.NumberFormat = "0.00"

End Sub

 

[Kerryx]

Thank you so much this worked a treat can you explain the formula structure if you have the time .
=COUNTIF(" & .Cells(1).Address & ":" & .Cells(1).Address(0, 0) & "," & .Cells(1).Address(0, 0) & ")>1")
This is great thank you again

 

[mse330]

Glad to help

You have defined your range rng by the InputBox and .Cells(1) means the first cell of the range and the .Address returns "$A$1" and I used .Address(0,0) to ignore absolute reference (returns "A1")

Run the below code to see what's the output of the formula

Sub TestAddress()

Dim rng As Range

Set rng = Application.InputBox(prompt:="Enter the range to check", Title:="Cell Reference", Type:=8)
With rng.Cells(1)
  MsgBox "=COUNTIF(" & .Address & ":" & .Address(0, 0) & "," & .Address(0, 0) & ")>1"
End With

End Sub

You can read more about .Address HERE

 

[Kerryx]

Really appreciate the help and the education , thank you

 

[mse330]

You are most welcome