Gareth

This should do the trick, change the color index to suit.

Sub Frmat()
Dim list As Range, crit As Range
Dim c
Set list = Range("A3:A101")
Set crit = Range("A2")
For Each c In list
If c.Value = crit Then
c.Interior.ColorIndex = 17
End If
Next c
End Sub

Regards
Peter
-----Original Message-----
When I apply the following format to the range A2:A101 it
works fine (if the
same value occurs more than once the cells are
highlighted).

=COUNTIF($A:$A,A2)1

I want to apply this format using code.

Thanks in advance.

Gareth


.

I think you want to set the color to a default color if not set to colorindex
of 17; otherwise, all cells that can get set would probably eventually
be set to 17 from previous or current assignments.

Another method is an Change Event macro, an example in
http://www.mvps.org/dmcritchie/excel/event.htm#case
a bit of overkill, but then I think you would have done fine with
conditional formatting. Conditional Formatting has the advantage
of not caring how the values in the cell changes whether manually,
by formula, or by pasting in but does have the limitation of 3
sets of C.F per cell.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Peter Atherton" wrote in message ...
Gareth

This should do the trick, change the color index to suit.

Sub Frmat()
Dim list As Range, crit As Range
Dim c
Set list = Range("A3:A101")
Set crit = Range("A2")
For Each c In list
If c.Value = crit Then
c.Interior.ColorIndex = 17
End If
Next c
End Sub

Regards
Peter
-----Original Message-----
When I apply the following format to the range A2:A101 it
works fine (if the
same value occurs more than once the cells are
highlighted).

=COUNTIF($A:$A,A2)1

I want to apply this format using code.

Thanks in advance.

Gareth


.