With a lot of help on another site, the following code was developed t
match the font color of certain cell data on one sheet with matchin
data in a database (i.e. if a name matches one in the database, matc
the font color of the name in the database).

Code 1 (this is inserted on a module and is the subject of my problem
because it uses a dictionary object):


Code
-------------------
Sub Namecolors(RngN As Range, RngD As Range)
Dim dicNames As New Dictionary
Dim c As Range

For Each c In RngN
If dicNames.Exists(c.Value) Then 'name in rngNames exists in dicNames
dicNames.Remove (c.Value) 'delete existing entry to read in new one
End If
dicNames.Add c.Value, c.Font.Color 'read name into dicNames and associate font color value
Next

For Each c In RngD
If dicNames.Exists(c.Value) Then 'cell value exists in dicNames
c.Font.Color = dicNames.Item(c.Value) 'set font color to associated value
Else
c.Font.Color = RGB(0, 0, 0) 'else, set font color to black
End If
Next
End Su
-------------------


Code 2 (inserted on the destination sheet … simply calls the Namecolor
code whenever a change occurs to the specified cells):


Code
-------------------
'Populate cells with matching font color
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, RngNames As Range, RngDesired As Range

Set RngDesired = Range("A1:D2")
Set RngNames = Worksheets("Extended List").Range("A1:B100")

On Error Resume Next
For Each c In RngDesired ' Cells.SpecialCells(xlCellTypeFormulas)
Call Namecolors(RngNames, RngDesired)
Next c
End Sub
-------------------


This code works great for what I need (i.e. it changes the font color
exactly as advertised). However, I would like it formatted such that i
does not require the use of a Dictionary object (i.e. I’m going to b
using this spreadsheet for communications purposes (i.e. sending it i
e-mails) and not everyone who opens it will have the Microsof
Scripting Runtime enabled (which is required for this code to work) an
I don’t want to have to explain how to enable it every time I send i
out)

--
BrianDP197
-----------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...fo&userid=2911
View this thread: http://www.excelforum.com/showthread.php?threadid=48856