Conditional formatting so that each row highlights duplicated values

Question

I have a spreadsheet with approx 300 rows. Each row has about 4 columns, and each cell contains a numeric value. Each row corresponds to a respondent.

I want to get Excel, for each respondent, to highlight cells if there are repeated values.

For example:

╔══════╦══════╦══════╦══════╗
║ Col1 ║ Col2 ║ Col3 ║ Col4 ║
╠══════╬══════╬══════╬══════╣
║    2 ║    4 ║   12 ║    9 ║
║    2 ║    5 ║   23 ║    5 ║
╚══════╩══════╩══════╩══════╝

Here I want both 5 to be highlighted, but not the 2s because they are in different rows. However, I have 400 rows and it will take too long to select each row individually and set the Conditional Formatting. On the other hand, if I select the entire range and apply Conditional Formatting, Excel calculates the duplicates in the whole range, which isn’t useful. Another user suggested using the below Macro, but it isn’t working:

Sub NewCF()
  Range("B1:P1").Copy
  For Each r In Selection.Rows
      r.PasteSpecial (xlPasteFormats)
  Next r
  Application.CutCopyMode = False
End Sub

I've tried the Macro above, but for some reason, when I run it, all it does is to underline the cells selected, even though the ones in my range are not underlined.

Any Ideas to do this in a efficient way?

Answer 1

Any Ideas to do this in a efficient way?

I would create a Conditional Formatting rule with the following formula and format to fill the cell. This formula compares rows individually, not the whole range at once. (Note: I am making the assumption you are familiar with Conditional Formatting)

Then in the Rules Manager, change the Applies to to the range you want this applied to; for instance, $A$1:$D$300.

This will apply this rule to that range and check each row using the formula you entered. This will highlight the duplicates in each row.

Answer 2

This is pretty easy using the name manager. (Name manager is on formula ribbon) Make a new name with the cursor in the top left cell of your data. In my example, the data is from e6 to h6, so I have my cursor in e6

• Test = IF(COUNTIF($E6:$H6,E6)>1,TRUE,FALSE)

What this is doing is counting how many other cells have the same value as the source cell. True means there is more than 1, false means there is not a duplicate Note: the name manager will expand this with the sheet names

select all the cells you want this to apply to, go to conditional formatting and select use an equation. The equation will be =test then select what formats you want it to use