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.

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.

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.

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.

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.