Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]() |
#9
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]() |
#12
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Isabella,
Thanks again !. regards, Johan |
#13
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() 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 | |
|
|
![]() |
||||
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) |