Dav,

The main reason I would like to do this in VBA code is that the
spreadsheet is used by a group of undisciplined users who very often
will blow away a whole section of a spreadsheet including the formating
using the copy and paste. If I control it thru VBA code I can have a
modicum of control. If you can help further, I would appreciate it very
much.

Thanks'

Old Dog'
Learning new tricks.


Dav wrote:
I am not sure why you need to write a macro, this seems to be
overcomplicating the process

In a cell lets say F3 create data,validation, list from somewhere else
in the spreadsheet, this will be your dropdownlist

In cell A1 goto format, conditional formating, choose formula is and
type
=isnumber(search($F$3,A1)) and choose a colour to change the cells to
highlight them


Then just copy this format by copy, paste special, formats to the other
cells a1:d4

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=573595