Add an .Offset. e.g.

Sheet2.Range(cell.Address).Offset(0,-1).Interior.ColorIndex =
cell.Interior.ColorIndex

NickHK

"Thulasiram" wrote in message
ups.com...
Nick,

In the code below, I am trying to shift the values, a cell before in
the other sheet. Cell value is transferredas expected but not the
colorindex.

Is there any command like (cell.address-1).. Please help.

Sheet2.Range("B1:I1").Value = Sheet1.Range("C1:J1").Value
For Each cell In Sheet1.Range("C1:J1")
Sheet2.Range(cell.Address).Interior.ColorIndex =
cell.Interior.ColorIndex
Next

Thanks,
Thulasiram

NickHK wrote:
If all the cell have the same .colorIndex, you code will work. However,
I
suspect this is not the case, hence the failure. So,

Dim Cell As Range

For Each Cell In Sheet1.Range("A1:A15")
Sheet2.Range(Cell.Address).Interior.ColorIndex =
Cell.Interior.ColorIndex
Next

NickHK

"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