Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Transfer the colorindex of a cell to another, without using paste or pastespecial
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 106
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 106
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
Fantastic Nick! Infact, I was tweaking something with For loop.. I
ended up facing errors. But, your code works perfect.. Nice usage of
the for loop for the question that I posted...

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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 106
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
Gary,

Thanks for ur initiative to help... I had a look at your code. It gave
an idea.. Nick's code solved the issue..

Gary Keramidas wrote:
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 106
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 106
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
Nick,

In the code below, I am trying to shift the values, a cell before in
the other sheet. Cell value is transferred as 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

Thulasiram wrote:
Gary,

Thanks for ur initiative to help... I had a look at your code. It gave
an idea.. Nick's code solved the issue..

Gary Keramidas wrote:
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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
look at the offset function


Sub test()

With Worksheets("Sheet1")
For Each cell In .Range("C1:J1")
Debug.Print cell.Address
Worksheets("Sheet2").Range(cell.Address).Offset(0, -1).Interior.ColorIndex =
3
Next
End With
End Sub
--


Gary


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

In the code below, I am trying to shift the values, a cell before in
the other sheet. Cell value is transferred as 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

Thulasiram wrote:
Gary,

Thanks for ur initiative to help... I had a look at your code. It gave
an idea.. Nick's code solved the issue..

Gary Keramidas wrote:
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




Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
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




Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 106
Default Transfer the colorindex of a cell to another, without using paste or pastespecial
Hearty Thanks to Nick and Gary.. Code works great..


Gary Keramidas wrote:
look at the offset function


Sub test()

With Worksheets("Sheet1")
For Each cell In .Range("C1:J1")
Debug.Print cell.Address
Worksheets("Sheet2").Range(cell.Address).Offset(0, -1).Interior.ColorIndex =
3
Next
End With
End Sub
--


Gary


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

In the code below, I am trying to shift the values, a cell before in
the other sheet. Cell value is transferred as 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

Thulasiram wrote:
Gary,

Thanks for ur initiative to help... I had a look at your code. It gave
an idea.. Nick's code solved the issue..

Gary Keramidas wrote:
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
transfer cell value from 1 sheet to another without copy/paste? sbmiller Excel Worksheet Functions 3 April 20th 10 06:51 PM
Paste vs PasteSpecial Gary''s Student Excel Programming 3 February 25th 06 06:29 PM
paste negative time with PasteSpecial Sylvian Excel Programming 20 September 2nd 05 06:03 AM
PasteSpecial Paste:=ColumnWidths Arvi Laanemets Excel Programming 3 February 8th 05 01:46 PM
PasteSpecial - Comments and ColorIndex JMay Excel Programming 6 November 20th 03 02:13 AM


All times are GMT +1. The time now is 08:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement