VBA -Conditional formatting -Moving reference

Dyl

New Member
Joined
Apr 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

1619433100675.png
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,874
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

Dyl

New Member
Joined
Apr 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 

Forum statistics

Threads
1,136,303
Messages
5,674,971
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top