Hello, big challange to make ! (for me). Perhaps somebody can help me out.


In Sheet1 I have 66 Columns and appr 60 records.
Column A = TextCode1 (like 3A, 3B, etc..),
Column B = TextCode2 (like A, B, etc..),
Column C till BN is heading nr.1 till 64.
In the records of Column C till BN are specific records only 'green' colored (filled color). There is no data/value registered in it. Only the color.

In Sheet2 I have the same columns but now started from Column K.
(TextCode1 in Column K, TextCode2 in Column L and the headings nr.1 till 64 in the Columns M till BX).
In sheet 2 are in the datafields of column M till BX values registered.

I like to have a module which;

Loop in Sheet2 from record 2 till last.
Based on TextCode1+TextCode2, Lookup in Sheet1 to the identical TextCode1+TextCode2 and then copy the colorformat from that row (from C till BN) from sheet 1 to this record in sheet2 in the columns M till BX of the specific record of the loop.
Based on the identical code, you will get then the same colored records.

Finally;
A module thats overlooks all records in Sheet2.
If the colored record is 'green' but there's no value registered, then color the field 'orange'.
If there's is a value registered in a field that isn't 'green' then color the record 'red'.

So,...... if there's a better creator then me :)
So please. Thanks !!

regards,
Johan.

i Johan,

if you could upload the file on (cjoint.com, or other ) course without
confidentielles' data, it would help us to help you!

isabelle

Le 2016-10-04 Ã* 12:01, a écrit :
Hello, big challange to make ! (for me). Perhaps somebody can help me out.


In Sheet1 I have 66 Columns and appr 60 records. Column A = TextCode1 (like
3A, 3B, etc..), Column B = TextCode2 (like A, B, etc..), Column C till BN is
heading nr.1 till 64. In the records of Column C till BN are specific records
only 'green' colored (filled color). There is no data/value registered in it.
Only the color.

In Sheet2 I have the same columns but now started from Column K. (TextCode1
in Column K, TextCode2 in Column L and the headings nr.1 till 64 in the
Columns M till BX). In sheet 2 are in the datafields of column M till BX
values registered.

I like to have a module which;

Loop in Sheet2 from record 2 till last. Based on TextCode1+TextCode2, Lookup
in Sheet1 to the identical TextCode1+TextCode2 and then copy the colorformat
from that row (from C till BN) from sheet 1 to this record in sheet2 in the
columns M till BX of the specific record of the loop. Based on the identical
code, you will get then the same colored records.

Finally; A module thats overlooks all records in Sheet2. If the colored
record is 'green' but there's no value registered, then color the field
'orange'. If there's is a value registered in a field that isn't 'green' then
color the record 'red'.

So,...... if there's a better creator then me :) So please. Thanks !!

regards, Johan.

Thanks.
Can you provide me an email adres were I can sent it to.
Or is there a possiblity to include attachements in this groups (I can find it).
You can send me the adress to

regards, Johan.

can you put a file on http://www.cjoint.com/
and return here to give us the download link
isabelle

Le 2016-10-08 Ã* 12:51, a écrit :
Thanks.
Can you provide me an email adres were I can sent it to.
Or is there a possiblity to include attachements in this groups (I can find it).
You can send me the adress to

regards, Johan.

Yes, It works :)

The download link is created. The file is downloadable within 21hrs.
In the file I'd explained my question clear with examples.
It's a bit changed then I'd asked earlier.
Hopely you can help me out. Thanks !

The link has been created: http://www.cjoint.com/c/FJjfVKxvOEJ

regards,
Johan

Chuyển nhÃ* thÃ*nh hÆ°ng Chuyển nhÃ* trá»�n góiPhó Thủ tÆ°á»›ng TrÆ°Æ¡ng Hòa Bình cÅ©ng đã chỉ đạo Bá»™ Công an khẩn trÆ°Æ¡ng thá»±c hiện ý kiến chỉ đạo của Tổng BÃ* thÆ° Nguyá»…n Phú Trá»�ng tại văn bản số 1578-CV/VPTW của Văn phòng Trung Æ°Æ¡ng Ä�ảng vá»� việc chỉ đạo cÆ¡ quan chức năng của Bá»™ Công an Ä‘iá»�u tra lÃ*m rõ các vi phạm dẫn đến thua lá»— gần 3.300 tá»· đồng giai Ä‘oạn 2012-2013 tại PVC, báo cáo Tổng BÃ* thÆ° vÃ* Thủ tÆ°á»›ng ChÃ*nh phủ.

Thanh tra ChÃ*nh phủ được giao nhiệm vụ tiếp tục lÃ*m rõ các vi phạm trong việc đầu tÆ°, thá»±c hiện các dá»± án của PVC giai Ä‘oạn 2008-2013, xác định rõ trách nhiệm của táº*p thể, cá nhân có liên quan, trong đó có trách nhiệm của ngÆ°á»�i đứng đầu vÃ* Ä‘á»� xuất xá»* lý, báo cáo Thủ tÆ°á»›ng ChÃ*nh phủ trong tháng 10 tá»›i.

Phó Thủ tÆ°á»›ng TrÆ°Æ¡ng Hòa Bình chỉ đạo Bá»™ TÃ*i chÃ*nh, Bá»™ Công ThÆ°Æ¡ng xác định việc lá»— lÅ©y kế gần 3.300 tá»· đồng của PVC giai Ä‘oạn 2012-2013 có bảo toÃ*n vốn hay không; có văn bản gá»*i Bá»™ Công an vÃ* Thanh tra ChÃ*nh phủ để phối hợp xá»* lý, báo cáo kết quả lên Thủ tÆ°á»›ng ChÃ*nh phủ Chuyển nhÃ* thÃ*nh hÆ°ng

hi Johan,

i do not good understand the conditions for colors (Column M and X till CU), i
hope you could adapt the macro for this part
otherwise, please return here asking for details ...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
TargetRow = Target.Row
rw = Application.Match(Range("I" & TargetRow), Sheets("Codes").Range("I:I"), 0)

'Step1: The existing filled colored in this sheet in column L and M and X till
CU, to change to 'no color'.
If Target.Column = 12 Then 'column "L"
If Sheets("Data").Range("L" & TargetRow) < "" Then
Sheets("Codes").Range("L" & rw & ":CU" & rw).Copy
Sheets("Data").Range("L" & TargetRow).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Else
Sheets("Data").Range("L" & TargetRow & ":CU" & TargetRow).Interior.Color =
xlNone
'clear the datas in this line (Mx:CUx), if date was cleared ?
End If
Target.Select
Application.EnableEvents = True
End If

'Step2:
If Target.Column = 13 Or (Target.Column = 24 And Target.Column <= 99) Then
'(Column M and X till CU)
datacolor = Sheets("Codes").Cells(rw, Target.Column).Interior.Color
Select Case datacolor
Case 15986394: Target.Interior.Color = 6750054
Case 16777215: Target.Interior.Color = 255
End Select
Application.EnableEvents = True
End If
End Sub

isabelle

Le 2016-10-09 Ã* 01:51, a écrit :
Yes, It works :)

The download link is created. The file is downloadable within 21hrs.
In the file I'd explained my question clear with examples.
It's a bit changed then I'd asked earlier.
Hopely you can help me out. Thanks !

The link has been created: http://www.cjoint.com/c/FJjfVKxvOEJ

regards,
Johan

if you could add table for colors, for example:
http://www.cjoint.com/c/FJkh34CPlOa

isabelle

sorry, for have forgetting a possibility
http://www.cjoint.com/c/FJkitFkiWMa
isabelle

Le 2016-10-10 Ã* 03:57, isabelle a écrit :
if you could add table for colors, for example:
http://www.cjoint.com/c/FJkh34CPlOa

isabelle

Isabelle, Thanks a lot.
You created it now as a ChangeWorksheet module. Can you please change it in a general one were I can push a button to run it. I will place it then under modules instead of direct under the datasheet.
ps. I do not realy understand your question about the color table. I have to read that first in more detail, and then I will come back on that later.

Le 2016-10-10 Ã* 07:20, a écrit : Isabelle, Thanks a lot.
You created it now as a ChangeWorksheet module. Can you please change it in a
general one were I can push a button to run it. I will place it then under
modules instead of direct under the datasheet. ps. I do not realy understand
your question about the color table. I have to read that first in more
detail, and then I will come back on that later.

voilÃ*,

Private Sub test()

green = 6750054
red = 255
blue = 15986394
orange = 49407
cNone = 16777215

TargetRow = Selection.Row
rw = Application.Match(Range("I" & TargetRow), Sheets("Codes").Range("I:I"), 0)

'Step1: The existing filled colored in this sheet in column L and M and X till
CU, to change to 'no color'.
If Selection.Column = 12 Then 'column "L"
If Sheets("Data").Range("L" & TargetRow) < "" Then
Sheets("Codes").Range("L" & rw & ":CU" & rw).Copy
Sheets("Data").Range("L" & TargetRow).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Else
Sheets("Data").Range("L" & TargetRow & ":CU" & TargetRow).Interior.color =
xlNone
'clear the datas in this line (Mx:CUx), if date was cleared ?
End If
Selection.Select
End If

'Step2:

For Each c In Sheets("Data").Range("M" & TargetRow & ":CU" & TargetRow)
rw1 = Application.Match(Range("I" & TargetRow),
Sheets("Codes").Range("I:I"), 0)
addr = Cells(rw1, Selection.Column).Address
CodeColor = Sheets("Codes").Range(addr).Interior.color

With Target.Interior
Select Case CodeColor
Case blue
If Target < 0 Then .color = green Else .color = CodeColor
Case cNone
If Target < 0 Then .color = red Else .color = orange
Case cNone
If Target = 0 Then .color = CodeColor
End Select
End With
Next

End Sub

isabelle

Isabella,

Thanks again !.

regards, Johan

Le 2016-10-13 Ã* 22:37, isabelle a écrit :
Le 2016-10-10 Ã* 07:20, a écrit : Isabelle, Thanks a lot.
You created it now as a ChangeWorksheet module. Can you please change it in a
general one were I can push a button to run it. I will place it then under
modules instead of direct under the datasheet. ps. I do not realy understand
your question about the color table. I have to read that first in more
detail, and then I will come back on that later.


forget the previous proposal, rather look this one
http://www.cjoint.com/c/FJonbgUBr8a

isabelle