Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 55
Default 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.




  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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.


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 55
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 55
Default 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   Report Post  
Banned
 
Posts: 1
Default

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 55
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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


  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 55
Default Colormarking of fields based on colors in another sheet(difficult !!)

Isabella,

Thanks again !.

regards, Johan
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change row colors based on a cell value JDaniel1127 Excel Worksheet Functions 0 March 27th 12 01:59 AM
Calculated Fields Based on Running Total Fields? Kruncher Excel Programming 0 May 12th 06 06:40 PM
Calculated Fields Based on Running Total Fields? Kruncher Excel Programming 0 May 12th 06 06:18 PM
Value based Text Colors JohnH[_4_] Excel Programming 4 June 8th 05 10:16 PM
How do I fill row colors based on cell value? Daskeeper Excel Discussion (Misc queries) 1 January 31st 05 11:12 AM


All times are GMT +1. The time now is 07:54 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"