Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109368 > unrolled thread

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

Back to article view | Back to microsoft.public.excel.programming

Contents

  Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-04 09:01 -0700
    Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-07 21:11 -0400
      Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-08 09:51 -0700
        Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-08 21:56 -0400
          Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-08 22:51 -0700
            Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-09 23:23 -0400
              Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-10 03:57 -0400
                Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-10 04:24 -0400
                  Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-10 04:20 -0700
                    Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-13 22:37 -0400
                      Re: Colormarking of fields based on colors in another sheet (difficult !!) jmslab@xs4all.nl - 2016-10-13 20:40 -0700
                      Re: Colormarking of fields based on colors in another sheet (difficult !!) isabelle <i@v.invalid> - 2016-10-14 09:01 -0400
    Re: Colormarking of fields based on colors in another sheet (difficult !!) xethubao 123 <xethubao 123@gmail.com> - 2016-10-10 02:38 +0100

#109368 — 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.

[toc] | [next] | [standalone]

#109380

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, jmslab@xs4all.nl 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.

[toc] | [prev] | [next] | [standalone]

#109381

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 jmslab@xs4all.nl

regards, Johan.

[toc] | [prev] | [next] | [standalone]

#109382

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, jmslab@xs4all.nl 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 jmslab@xs4all.nl
>
> regards, Johan.
>
>

[toc] | [prev] | [next] | [standalone]

#109384

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

[toc] | [prev] | [next] | [standalone]

#109391

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, jmslab@xs4all.nl 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
>

[toc] | [prev] | [next] | [standalone]

#109393

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

isabelle

[toc] | [prev] | [next] | [standalone]

#109394

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

[toc] | [prev] | [next] | [standalone]

#109401

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.

[toc] | [prev] | [next] | [standalone]

#109427

Le 2016-10-10 à 07:20, jmslab@xs4all.nl 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

[toc] | [prev] | [next] | [standalone]

#109428

Isabella,

Thanks again !.

regards,  Johan

[toc] | [prev] | [next] | [standalone]

#109430

Le 2016-10-13 à 22:37, isabelle a écrit :
> Le 2016-10-10 à 07:20, jmslab@xs4all.nl 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

[toc] | [prev] | [next] | [standalone]

#109390

'Chuyển nhÃ* thÃ*nh hÆ°ng ' (http://tinyurl.com/gob5ry7) 'Chuyển
nhÃ* trọn gói' (http://dichvuchuyennhatrongoi.org.vn/)Phó 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 '
(http://chuyennhathanhhunghanoi.com/)




-- 
xethubao 123

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web