Colormarking of fields based on colors in another sheet (difficult !!)
 

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.

Colormarking of fields based on colors in another sheet(difficult !!)
 

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.

Colormarking of fields based on colors in another sheet(difficult !!)
 

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.

Colormarking of fields based on colors in another sheet(difficult !!)
 

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.

Colormarking of fields based on colors in another sheet(difficult !!)
 

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

Colormarking of fields based on colors in another sheet(difficult !!)
 

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

Colormarking of fields based on colors in another sheet(difficult !!)
 

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

isabelle

Colormarking of fields based on colors in another sheet(difficult !!)
 

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

Colormarking of fields based on colors in another sheet(difficult !!)
 

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.

Colormarking of fields based on colors in another sheet(difficult !!)
 

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

Colormarking of fields based on colors in another sheet(difficult !!)
 

Isabella,

Thanks again !.

regards, Johan

Colormarking of fields based on colors in another sheet(difficult !!)
 

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