highlighting duplicate values

  • Thread starter Thread starter Alice
  • Start date Start date
A

Alice

For example,

A1 = 5
A2 = 6
A3 = 7
A4 = 5
A5 = 6

How can you highlight/change the cell colour of all duplicate
occurences of A1 in the row, in this case A4?

And when changed is it possible to keep the highlighted colour, in A4,
even if A1 is then changed to 6? and when it changes to again
highlight/change the colour of all cells in the column that match its
new number - in this case A2 and A5?

-----
i'm completely new to spreadsheets and feel a bit in over my head.

thanks,

Alice
 
One way, via conditional formatting

Select A2:A5
(ensure A2 is active)

Click Format > Conditional Formatting

Under Condition 1, make the settings as:
Formula is: =AND(ISNUMBER(MATCH($A$1,A2,0)),$A$1<>"")
Click Format button > Patterns tab > Green? > OK

Click OK at the main dialog

The above will trigger the desired cond formats within A2:A5,
depending on the input in A1
 
Line:
Formula is: =AND(ISNUMBER(MATCH($A$1,A2,0)),$A$1<>"")
Click to expand...

should read as:
Formula is: =AND($A$1=A2,$A$1<>"")

(the former works as well but it was overkill, sorry)

---
 
Back
Top