Worksheet Range Question - Excel 2003

J

jfcby

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
 
D

Dave Peterson

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.)
 
J

jfcby

Hello,

Thank you for the macro code it works great!

Can this code be modified to change font color of certain words using
the same range but with up to five words in one cell.

Thanks for your help,
Frankie
 
D

Dave Peterson

Probably not by modifying the existing macro. I think you'd have to use a
different macro that separated the names into pieces and then looked at each of
those pieces.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

shorten sheet name 4
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
Changing cell colour depending on content?? 7
Copy and paste entire line starting at cell W 5
Run-time error 9 6
Range on Userform only if on worksheet 1
Excel VBA 1
Looping Through Worksheets Given in a List? 5

Top