Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colormarking of fields based on colors in another sheet(difficult !!)
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colormarking of fields based on colors in another sheet(difficult !!)
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colormarking of fields based on colors in another sheet(difficult !!)
Isabella,
Thanks again !. regards, Johan |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change row colors based on a cell value | Excel Worksheet Functions | |||
Calculated Fields Based on Running Total Fields? | Excel Programming | |||
Calculated Fields Based on Running Total Fields? | Excel Programming | |||
Value based Text Colors | Excel Programming | |||
How do I fill row colors based on cell value? | Excel Discussion (Misc queries) |