how do i shade all identical cells automatically
 

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.

how do i shade all identical cells automatically
 

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


--
Regards,

Peo Sjoblom

"eve" wrote in message
...
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.

how do i shade all identical cells automatically
 

eve

Use Conditional Formatting.

Select a range of cells, say A1:M100 then FormatCFFormula is

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

Pick a pattern and OK


Gord Dibben MS Excel MVP

On Thu, 3 May 2007 14:27:02 -0700, eve wrote:

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.

how do i shade all identical cells automatically
 

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.

"eve" wrote:

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.

how do i shade all identical cells automatically
 

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

On Thu, 3 May 2007 23:19:01 -0700, eve wrote:

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.

"eve" wrote:

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.