i have something similar for a client. they use box codes and are listed in
u1:u8.
this code determines the cell colors and then formats the cells in u24:u357
accordingly. maybe it will give you an idea.

Set rng = ws.Range("U24:U357")

With rng
For Each cell In rng
Select Case cell.Value
Case "10R"
fClr = ws.Range("U1").Font.ColorIndex ' 2 'white
bClr = ws.Range("U1").Interior.ColorIndex ' 9 ' brown
Case "BAB"
fClr = ws.Range("U2").Font.ColorIndex ' 2 ' white
bClr = ws.Range("U2").Interior.ColorIndex ' 1 ' black
Case "BDM"
fClr = ws.Range("U3").Font.ColorIndex ' 1 'black
bClr = ws.Range("U3").Interior.ColorIndex ' 2 'white
Case "G-2"
fClr = ws.Range("U4").Font.ColorIndex ' 1 'black
bClr = ws.Range("U4").Interior.ColorIndex ' 3 'red
Case "Per-2"
fClr = ws.Range("U5").Font.ColorIndex ' 1 'black
bClr = ws.Range("U5").Interior.ColorIndex ' 48 'gray
Case "RT24"
fClr = ws.Range("U6").Font.ColorIndex ' 1 'black
bClr = ws.Range("U6").Interior.ColorIndex ' 39 'violet
Case "W-2" ' change color codes below
fClr = ws.Range("U7").Font.ColorIndex ' 2 'white
bClr = ws.Range("U7").Interior.ColorIndex ' 11 'dk blue
Case "X-2"
fClr = ws.Range("U8").Font.ColorIndex ' 1 'black
bClr = ws.Range("U8").Interior.ColorIndex ' 43 'green
End Select
cell.Interior.ColorIndex = bClr
cell.Font.ColorIndex = fClr
Debug.Print bClr & " " & fClr
Next
End With

--


Gary


"Thulasiram" wrote in message
oups.com...
Hello people,

I would like to transfer the format of the cells (value and colorindex)
"without" using pastespecial or paste command. I have written a code
like this

Sub test()
Sheet2.Range("A1:A15").Value = Sheet1.Range("A1:A15").Value
Sheet2.Range("A1:A15").Interior.ColorIndex =
Sheet1.Range("A1:A15").Interior.ColorIndex
End Sub

Values are transferred but not the colorindex. How could the coloindex
of the cells be transferred without using the pastespecial command?

Please provide your help..

Thanks,
Thulasiram