Worksheet Range Question - Excel 2003
 

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

Worksheet Range Question - Excel 2003
 

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

Worksheet Range Question - Excel 2003
 

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

Dave Peterson wrote:
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

Worksheet Range Question - Excel 2003
 

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.



jfcby wrote:

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

Dave Peterson wrote:
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

--

Dave Peterson

Worksheet Range Question - Excel 2003
 

Hello Dave,

Thank you for all your help!

Wanted to let you know that as I was searching the news group I found
one of your codes that will work changing the font color on serval
words in one cell.

http://groups.google.com/groups?thre...03A7%40msn.com

Thanks,
Frankie


Dave Peterson wrote:
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.



jfcby wrote:

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

Dave Peterson wrote:
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

--

Dave Peterson

Worksheet Range Question - Excel 2003
 

Google is nice, huh?

jfcby wrote:

Hello Dave,

Thank you for all your help!

Wanted to let you know that as I was searching the news group I found
one of your codes that will work changing the font color on serval
words in one cell.

http://groups.google.com/groups?thre...03A7%40msn.com

Thanks,
Frankie

Dave Peterson wrote:
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.



jfcby wrote:

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

Dave Peterson wrote:
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

--

Dave Peterson

--

Dave Peterson