how do i shade all identical cells automatically

[Guest]

I want to shade all the identical cells in a large spread sheet to pick out a
pattern How can I do this without having to do it all individually. Many
thanks for any help.

 

[Peo Sjoblom]

What do you mean by that, do you want to colour all cells with values that
are not unique? Assume the range is A1:G7, do format > conditional
formatting, formulas is

=COUNTIF($A$1:$G$7,A1)>1

click the format button and select a pattern, then click OK twice

 

[Gord Dibben]

eve

Use Conditional Formatting.

Select a range of cells, say A1:M100 then Format>CF>Formula is

=IF(COUNTIF(A1:M1000, A1)>1,TRUE,FALSE)

Pick a pattern and OK


Gord Dibben MS Excel MVP

 

[Guest]

thank you for that but what I meant was that I want all the cells with A in
say in red, all the cells with B in say in blue etc etc. The spread sheet
is quite big so doing it individually will be a nightmare.

 

[Gord Dibben]

eve

This macro will do the trick.

Just add more nums and vals to cover etc., etc.

Sub foo()
Set r = Range("A1:M300")

vals = Array("A", "B", "C", "D", "E", "F", "G")
nums = Array(8, 9, 6, 3, 7, 4, 20) ' colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

To get a list of numbers for the color palette see David McRitchie's site or go
into the VBE and VBA help under "PatternColorIndex Property".


Gord