Paul,
thanks for the pointer. Although I don't fully understand the VB code, if
the comments in the example code are anything to go by this would perform the
color copying from the cell on sheet1 to a different cell on sheet2.
The part I am still missing is how to read the cell contents on sheet1 and
use that to determine the target cell location on sheet2. I will quote the
simplified example from my earlier post ..
Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
Step2. Check the color format of the cell two columns to the right
(colored RED)
Step3. Go to Sheet2, color the cell whose reference was determined in
Step1 (C3) the color identified in Step2.
Step4. Read the cell value for the second item in Sheet1 column A (A2
value = "D2")
Step5. Check the color format of the cell two columns to the right
(colored BLUE)
Step6. Go to Sheet2, color the cell whose reference was determined in
Step4 (D2) the color identified in Step5.
Step7. Read the cell value for the third item in Sheet1 column A (A2 value
= "A4")
etc,
etc
regards,
Kevin
paul said:
Kevin/Gordon this
http://www.microsoft.com/office/com...misc&mid=573ae59f-ded9-4dad-aa11-2c0acdd69cbc
thread lead me to this
http://www.mvps.org/dmcritchie/excel/colors.htm
page,and the macro below it looks like this could be what kevin is after???
Setting Interior Color based on another Cell (#popbased)
Option Explicit
Global gblColorIndex As Integer
Sub SetInteriorColor()
gblColorIndex = ActiveCell.Interior.ColorIndex
End Sub
Sub PutInteriorColor()
Selection.Interior.ColorIndex = gblColorIndex
End Sub
Sub SameInteriorAsA1()
Selection.Interior.ColorIndex = [A1].Interior.ColorIndex
End Sub
--
paul
remove nospam for email addy!
kevinm said:
Gord,
no worries, a few days isn't going to cause me any problem. Besides, when we
have this working it will probably save me days of work and pain anyway,
Kevin
:
Kevin
You've not been forgotten.
Been busy but will try to get you something in the next day or so.
Monday I'm starting a Consulting contract and will be out of town for most of
every week until end of September so I better get you something before then.
Gord
Hi Paul,
thanks for the suggestion, I am certainly willing to give is a try but how
do I create this function, is it just another macro? Do I have to use the VB
editor to type in the function by hand?
sorry for the stupid questions, I have not been to this depth with Excel
before,
Kevin
:
How about this for an idea.Have a custom worksheet function called say colour
where a a cell (or selected range?) is shaded in relation to a certain
value.I had a go using this code but it didnt like it
Function colour()
colour = Cells.Interior.
.ColorIndex = 1
.Pattern = xlSolid
End Function
=colour(1) would result in the selected cell or cells being shaded in black
the values on sheet 1 could then be linked to the cells on sheet two to give
the result kevin wants.I think perhaps it could be a function that would be
more flexible than conditional formatting.
--
paul
remove nospam for email addy!
:
Gord,
I am glad you understand my requirements now. It sounds so simple to do when
I explain it in simple terms but I haven't been able to figure it out.
If it is not possible I will just have to create a huge VBA file with a
separate procedure defined for every cell in column A, this is going to be
extremely tedious and error prone. In the real spreadsheet column A has about
600 rows to process!
If you (or anyone else reading watching this forum) can figure it out it
will save me an awful lot of time. Fingers crossed ..
Kevin
:
Kevin
I'd have to look at this one for a while. VBA is not my strong suit.
Stick around here and wait for someone(there are many) with greater skills
than myself.
Meantime, I'll try to work on it between Tee-Times and other projects.
Gord
Hi Gord,
sorry I am doing a poor job explaining my requirements, it is difficult
without actually sending you an example spreadsheet. Let me have another go
at explaining, consider this simplified example:
Sheet1 looks something like:
Column
A B C D E F
Row 1 C3 test Red_Cell
Row 2 D2 test Blue_Cell
Row 3 A4 test Green_Cell
Row 4 B3 test Pink_Cell
Initially Sheet2 looks like:
Column
A B C D E F
Row 1
Row 2
Row 3
Row 4
I would like a macro which work something like:
Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3")
Step2. Check the color format of the cell two columns to the right
(colored RED)
Step3. Go to Sheet2, color the cell whose reference was determined in
Step1 (C3) the color identified in Step2.
Step4. Read the cell value for the second item in Sheet1 column A (A2
value = "D2")
Step5. Check the color format of the cell two columns to the right
(colored BLUE)
Step6. Go to Sheet2, color the cell whose reference was determined in
Step4 (D2) the color identified in Step5.
Step7. Read the cell value for the third item in Sheet1 column A (A2 value
= "A4")
etc,
etc
When the macro has finished Sheet2 should look like:
Column
A B C D E F
Row 1
Row 2 (blue)
Row 3 (pink) (red)
Row 4 (green)
Where (xxx) is the color fill of the cell, not the value for the cell
contents.
I hope that my explanation is a little clearer this time,
Kevin
:
Kevin
Still not sure what your needs are. Copy cells or copy formats?
Also "a number of them are grouped into 20 consecutive cells".
How many and what is the criterion for deciding which 20 to copy?
In blocks of 20 cells to where?
This macro will copy cells only to another worksheet.
Sub move20()
ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1")
End Sub
Probably not what you want, but a start.
No need for a new thread unless you want to post over in the excel.programming
group, but everyone over there reads this group also.
Gord
Gord/Myrna,
thanks for your help, I went back and recreated the macro, I don't know what
I did different but it is working now.
All I need to figure out now is how to repeat the macro operation a number
of times.
Consider:
My master worksheet has about 600 cells, all in one column. Of these a
number of them are grouped into 20 consecutive cells. Instead of executing
the macro twenty times to copy the 20 cells to worksheet 2, is there a way of
looping the macro a user specified number of times?
Let me know if I should start a new thread for this,
thanks,
Kevin
:
Kevin
Errors out on this line most likely..........
ActiveCell.Offset(-9, 0).Range("A1").Select
If the activecell is in above row 10 this will throw an error because you are
trying to select from 9 rows above the activecell.
The -9 moves up 9 rows, the 0 means column remains unchanged.
What your macro is doing is selecting a cell 9 rows above the active cell then
copying the format of that cell and pasting into the active cell then
