Thank you Stefi,
and thank you for your attetion to my questions, you are right about
changing color that is related to my used version (2007) and as you said it
is working perfectly in previous version.
Many Regards
Bijan
"Stefi" wrote:

Hi Bijan,

You can enhance the list of properties to be transferred as you like, see
this new version:

Sub szinez()
Dim colD As Range
noofrows = Columns("D:D").End(xlDown).Row
Set colD = Range("D2:D" & noofrows)
For Each dcell In colD
hit = WorksheetFunction.Match(dcell.Value, _
Worksheets("pattern").Columns("A:A"), 0)
bgcolorno = Worksheets("pattern").Range("A" &
hit).Interior.ColorIndex
fgcolorno = Worksheets("pattern").Range("A" & hit).Font.ColorIndex
fntsno = Worksheets("pattern").Range("A" & hit).Font.Size
Range("A" & dcell.Row & ":K" & dcell.Row).Interior.ColorIndex =
bgcolorno
Range("A" & dcell.Row & ":K" & dcell.Row).Font.ColorIndex =
fgcolorno
Range("A" & dcell.Row & ":K" & dcell.Row).Font.Size = fntsno
Next dcell
End Sub

I can't imagine why does the macro change pattern colors with you, it works
perfectly for me. Which Excel version do you use? Send me your workbook with
the embedded macro to this address: , I'll try to find
it out.

Regards,
Stefi


€žbijan€� ezt Ã*rta:

Thank you Stefi
apologize me for responsing late, the code worked and colored my list, but
some thing is strange ,after run the code some of the colors created in the
list are different from my defination, for example light orange and light
gray both appear light yellow ,
indeed,if it possible I want to know can I define some properties such as
forcolor or fontsize with my words in pattern sheet at same time.
many regards
Bijan
"Stefi" wrote:

hit = WorksheetFunction.Match(dcell.Value, _
Worksheets("pattern").Columns("A:A"), 0)

This must be one line in VBA, and as I see, this message window broke it
into two parts. I Inserted a line break to avoid it. Have you named the
helper sheet "pattern"?

Sub szinez()
Dim colD As Range
noofrows = Columns("D:D").End(xlDown).Row
Set colD = Range("D2:D" & noofrows)
For Each dcell In colD
hit = WorksheetFunction.Match(dcell.Value, _
Worksheets("pattern").Columns("A:A"), 0)
colorno = Worksheets("pattern").Range("A" & hit).Interior.ColorIndex
' dcell.EntireRow.Interior.ColorIndex = colorno
Range("A"&dcell.Row&":K"&dcell.Row).Interior.Color Index = colorno
Next dcell
End Sub


Regards,
Stefi

€žbijan€� ezt Ã*rta:

Stefi
Genius Thanks, I have done what you said, But I am reciveing an
error(run-time error '1004') in line hit=.............. with just one row
colored in top of my list,and
is it posible to color a range(A:K) instead of entire row?
A.....................D........................... .......K
1 ......................USS......................... ......
RED
2 ......................NZZ......................... .......
BLUE

Bijan
Stefi" wrote:

Place your words in a helper column, in my example it is column A in a helper
worksheet named "pattern", set the backgrond color of cells to the required
color for the word in them, and run this macro:

Sub szinez()
Dim colD As Range
noofrows = Columns("D:D").End(xlDown).Row
Set colD = Range("D2:D" & noofrows)
For Each dcell In colD
hit = WorksheetFunction.Match(dcell.Value,
Worksheets("pattern").Columns("A:A"), 0)
colorno = Worksheets("pattern").Range("A" & hit).Interior.ColorIndex
dcell.EntireRow.Interior.ColorIndex = colorno
Next dcell
End Sub

Regards,
Stefi


€žbijan€� ezt Ã*rta:

Hi all,
could someone please help me to write a vb code with SELECT CASE or IF
statment? I have a sheet with about 5000 rows (A2:K5000)and use some words
like XTT,YBB,NZZ,USS,...(about 10 words) in column D .
what I need to do is each row that contain these word have spetial color in
list for example:
.....................D..............
.....................NZZ.......... red
.....................XTT......... blue
Thanks in advance