I have two attempts here that are not falling into place for me.
(The sheet names are a bit goofy, but real names in this test workbook.)

Column G on sheet "name1" gets a list of numbers with several repeats.
On all the other sheets (except sheet "dont touch this sheet") there are many number on each sheet.

For each unique number on sheet "name1" if it occurs on any of the other sheets then color the font the same color. (If I can get it to do the font then I can swap to color the cell if I prefer)

So I have some very scant results with the two attempts below.
First I make a unique list in column F the shoot for a For Each loop on that F column and then a For Each loop on the worksheets and increment the colorindex by 1 for each number, which starts at colorindex 3.

I'm getting a couple of colors on one or two other sheets and some of the different numbers on the others sheet are the same color. Its pretty screwy.

In the second code this errors With Sheets(varSheets(i))

I intend to delete the F column list after the code runs successfully.
I have verified that the numbers are really numbers by using an =SUM(....) on the them.

Thanks,
Howard

Sub SearchColor()
Dim ws As Worksheet
Dim lrow As Long
Dim CheckNum As Range
Dim i As Long
Dim frow As Long
Dim c As Range
Dim cc As Long

lrow = Cells(Rows.Count, "G").End(xlUp).Row
Range("G2:G" & lrow).Copy Range("F" & Rows.Count).End(xlUp)(2)
Range("F2:F" & lrow).RemoveDuplicates 1
frow = Cells(Rows.Count, "G").End(xlUp).Row

For Each CheckNum In Range("F2:F" & frow)
cc = 3
For Each ws In ThisWorkbook.Sheets
If (ws.Name < "dont touch this sheet") And (ws.Name < "name1") Then
With ws
Set CheckNum = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)
If Not CheckNum Is Nothing Then
CheckNum.Font.ColorIndex = cc
' CheckNum.Interior.ColorIndex = cc
End If
End With
End If
Next 'ws
cc = cc + 1
Next 'c
End Sub


Sub ColorNumCells()
Dim ws As Worksheet
Dim lrow As Long
Dim CheckNum As Range
Dim varSheets As Variant
Dim i As Long
Dim frow As Long
Dim cc As Long

lrow = Cells(Rows.Count, "G").End(xlUp).Row
Range("G2:G" & lrow).Copy Range("F" & Rows.Count).End(xlUp)(2)
Range("F2:F" & lrow).RemoveDuplicates 1
frow = Cells(Rows.Count, "G").End(xlUp).Row

varSheets = Array("name", "another name", "etc.", "etc..", "etc....")
cc = 3
For i = LBound(varSheets) To UBound(varSheets)
For Each CheckNum In Range("F2:F" & frow)
With Sheets(varSheets(i)) '/error here

Set CheckNum = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)

If Not CheckNum Is Nothing Then
'CheckNum.Interior.ColorIndex = cc
CheckNum.Font.ColorIndex = cc
End If

End With
Next 'Each
cc = cc + 1
Next 'i

End Sub

You need to instantiate separate range vars so you're not
using/changing *CheckNum* in every iteration of your loop. So...

For Each CheckNum...
'..some code
Set CheckNum = .UsedRange.Find(What:=CheckNum...

How about something like this...

For Each CheckNum...
'..some code
Set rngFound = .UsedRange.Find(What:=CheckNum.Value...

-OR-

For Each rng...
'..some code
Set rngFound = .UsedRange.Find(What:=CheckNum.Value...

...not just so *you* know what your code is doing, but so *your code
knows* what it's supposed to be doing!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

For Each rng...

'..some code

Set rngFound = .UsedRange.Find(What:=CheckNum.Value...



..not just so *you* know what your code is doing, but so *your code

knows* what it's supposed to be doing!<g



--

Garry

Thanks for looking in, Garry.

This does not error but also does not do anything. Seems like I should say something like For Each numFnd.Font.ColorIndex = cc but I would think the With ws would do that.

Howard

Sub SearchColor()
Dim ws As Worksheet
Dim lrow As Long
Dim CheckNum As Range
Dim i As Long
Dim frow As Long
Dim c As Range
Dim cc As Long
Dim numFnd As Range

lrow = Cells(Rows.Count, "G").End(xlUp).Row
Range("G2:G" & lrow).Copy Range("F" & Rows.Count).End(xlUp)(2)
Range("F2:F" & lrow).RemoveDuplicates 1
frow = Cells(Rows.Count, "G").End(xlUp).Row

For Each CheckNum In Range("F2:F" & frow)
cc = 3
For Each ws In ThisWorkbook.Sheets
If (ws.Name < "dont touch this sheet") And (ws.Name < "name1") Then
With ws
Set numFnd = .UsedRange.Find(What:=CheckNum.Value, LookIn:=xlValues)
If Not numFnd Is Nothing Then
numFnd.Font.ColorIndex = cc
End If
End With
End If
Next 'ws
cc = cc + 1
Next 'c
End Sub

I'm working on something for you to try. Bear with me and I'll post
shortly...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Try...

Sub SearchColor()
Dim wks As Worksheet
Dim rng, rngFound, vCheckRng
Dim lLastRow&, n&, lFirstRow&, lColor&

Const sSheetsToOmit$ = "dont touch this sheet,name1"

lLastRow = Cells(Rows.Count, "G").End(xlUp).Row
Range("G2:G" & lrow).Copy Range("F" & Rows.Count).End(xlUp)(2)
Range("F2:F" & lrow).RemoveDuplicates 1
lFirstRow = Cells(Rows.Count, "G").End(xlUp).Row

vCheckRng = Range("F2:F" & lFirstRow): lColor& = 3
For n = LBound(vCheckRng) To UBound(vCheckRng)
For Each wks In ThisWorkbook.Sheets
If InStr(1, sSheetsToOmit, wks.Name) = 0 Then
Set rngFound = wks.UsedRange.Find(What:=vCheckRng(n, 1),
LookIn:=xlValues)
If Not rngFound Is Nothing Then
rngFound.Font.ColorIndex = lColor
' rngFound.Interior.ColorIndex = lColor
End If
End If
Next 'ws
lColor = lColor + 1
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Sub SearchColor()

Dim wks As Worksheet

Dim rng, rngFound, vCheckRng

Dim lLastRow&, n&, lFirstRow&, lColor&



Const sSheetsToOmit$ = "dont touch this sheet,name1"



lLastRow = Cells(Rows.Count, "G").End(xlUp).Row

Range("G2:G" & lrow).Copy Range("F" & Rows.Count).End(xlUp)(2)

Range("F2:F" & lrow).RemoveDuplicates 1

lFirstRow = Cells(Rows.Count, "G").End(xlUp).Row



vCheckRng = Range("F2:F" & lFirstRow): lColor& = 3

For n = LBound(vCheckRng) To UBound(vCheckRng)

For Each wks In ThisWorkbook.Sheets

If InStr(1, sSheetsToOmit, wks.Name) = 0 Then

Set rngFound = wks.UsedRange.Find(What:=vCheckRng(n, 1),

LookIn:=xlValues)

If Not rngFound Is Nothing Then

rngFound.Font.ColorIndex = lColor

' rngFound.Interior.ColorIndex = lColor

End If

End If

Next 'ws

lColor = lColor + 1

Next 'n

End Sub

Garry

Garry,

Getting some scattered results, where a sheet is apparently bypassed, the next has a couple correct colors and the next seems to color one of each of the list in F.

I'm using as test numbers on sheet name1:

1
2
3
1
2
3
1
2
3

and on the other sheets the same list extended by to present some numbers to skip/omit:

6
7
8
6
7
8

The goal being that if there is a 1 on name1 sheet all 1's on all the other sheets should be the same color and the 2's all the same color etc.

The last sheet to have qualifying numbers had the most numbers correctly colored but not all qualifiers were colored.

I was able enough to fix the

Range("G2:G" & lrow).Copy

to

Range("G2:G" & lLastRow).Copy

but not able enough to shake up the code with any confidence.

Howard