How about:

Option Explicit
Sub ColorBackground2a()
'Color background of specfic words
Dim Rng As Range
Dim NamesRng As Range
Dim myCell As Range
Set NamesRng = Worksheets("macros").Range("Names")

Set Rng = ActiveSheet.Range("D7:D22")

For Each myCell In Rng.Cells
If Application.CountIf(NamesRng, myCell.Value) 0 Then
myCell.Interior.ColorIndex = 35
myCell.Interior.Pattern = xlSolid
End If
Next myCell
End Sub

But you could use the same kind of thing by just applying format|conditional
formatting

Select D7:D22
and with D7 the activecell
format|conditional formatting
Formula is:
=COUNTIF(Names,D8)0

And give it a nice format.

(This assumes that Names is a workbook level name, too.)



jfcby wrote:

Hello,

On worksheet 1 my range name will be NAMES, there will be about 25. On
worksheet 2 there will be 100 different names on A3:K3. I would like to
have a background color in the cells that have the range names?

This is the macro error message I'm getting:

Run-Time Error '13':
Type Mismatch

This is the line the Debug highlights:

If mycell.Value = Range("MACROS!NAME") Then

Here is the macro code:

Sub ColorBackground2()
'Color background of specfic words
Dim Rng As Range
Dim mycell
Set Rng = ActiveSheet.Range("D7:D22")
For Each mycell In Rng
If mycell.Value = Range("MACROS!NAME") Then '<< ERROR ON THIS LINE
mycell.Select
With Selection
mycell.Interior.ColorIndex = 35
mycell.Interior.Pattern = xlSolid
End With

End If
Next mycell
End Sub

Thanks for help in advance
Frankie

--

Dave Peterson