VBA -Conditional formatting -Moving reference

[Dyl]

Hi everyone,
I currently have a macro that inserts a line below the current selected row, and copies that row into the newly inserted row. It also changes the text in column "A" to grey in the copies.
In column "C" is a value that the user then needs to update with information gathered elsewhere.
I would like to highlight the value in column "C" if it is still equal to any other value in column "C" but only if the value in column "A" matches another row as well.
Below shows roughly what I mean. The 20's in column C3 and C4 are highlighted because they match along with A3 and A4.
Ignore column B.

I have the following formula that can go into conditional formatting but I don't know how to add this formula into the macro.
=COUNTIFS($A:$A,$A2,$C:$C,$C2)>1 (This would be relevant in row2
The formula just ends up looking at the same cells in row 2 instead of moving the reference to rows 3, 4, 5 etc...

Thank you to anyone than can help me with this.

 

[yky]

Do you know how to record a macro? Go to Developer and select "Record Macro". Then, do what you would normally do. After that, open the macro and see the code. Usually, the code needs to be edited to make it more efficient but it should work as it is.

The following is what I recorded.

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIFS($A:$A,$A2,$C:$C,$C2)>1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Dyl]

Do you know how to record a macro? Go to Developer and select "Record Macro". Then, do what you would normally do. After that, open the macro and see the code. Usually, the code needs to be edited to make it more efficient but it should work as it is.

The following is what I recorded.

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIFS($A:$A,$A2,$C:$C,$C2)>1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub
[/CODE
[/QUOTE]

Thanks yky,
That seems to work. I did try something like that previously but for some reason it just kept referring to C2 on every row instead of moving down to C3, C4, C5 etc... You solution will save me a lot of time and prevent errors uploading data from my spreadsheet into other software.