Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
Is there a way to highlight cells or rows that contatin the same info? I am having problems with conditional formating. My cols. are as follows, A: First name, B:Last Name, C:Street Address - pretty simple. Any way to compare the value of A1:A2 to B1:B2 and cause one or both rows/cells to highlight. Same with cell C - if addys match, highlight that cell also. I have come very close, but I can't seem to base the values on a variable. THe only way I have made it work is to set a value that everything has to be equal to. I tried higlighting all three columns and setting the "formula is" =if(a1=a2,1,0) for condition 1, if(b1=b2,1,0) for condition 2. This got me close, but everything was getting highlighted with the actually duplicates not highlighting, and surprisingly, only columns a & c were being formated....any suggestions??
Thanks in advance.
You might want to try the macro below. I got that from somebody and should credit that person for it (can't remember his name, sorry).
Make sure you sort your list first. The macro works for column A, but you can change it in such a way that it runs it for any other column.
Goodluck
Steven
*************************
Sub HighlightDups()
'Highlights all duplicates from a list of numbers
'First SORT the list in column A, otherwise the procedure will fail
rCount = Application.CountA(ActiveSheet.Range("A:A")) 'Get row count before beginning
For LoopCount = 1 To 6 'Spin thru 6 times to get them all
For myRow = 2 To rCount 'Main loop
If Cells(myRow, 1) = Cells(myRow - 1, 1) Then 'Check for dups
Cells(myRow, 1).Select 'Select the cell
Selection.Interior.ColorIndex = 6
Qq = Qq - 1 'Change loop criteria because cell gone
End If 'End
Next 'Inside loop
Next 'Outside loop
End Sub
****************************************
You can do step-by-step as below :
1. sort the data first
2. select the entire column which must be a Key of each row (record)
3. select "Conditional Formatting" under the Format Menu
4. select "Formula" from the Condition 1
5. type the following formula on the next field
=A1=A65536
6. click on "Format" Button to create your favour pattern
7. press OK - the duplicated record will be hightlighted.
** Means to check whether the active cell is equal to the last cell of previous row.
My compliments for this beautiful solution ! I'll add this one to my "excel knowledge database" !