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
this is what i am trying to accomplish
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
VBA Code:
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