Following the completion of a my book, how do I get automatic coloring as fontcolors of tables?
Following the completion of a my book, how do I get automatic coloring as fontcolors of tables?
Last edited by Immortal2014; 11-27-2016 at 06:48 AM. Reason: I update the details in the wb
Nick
This example uses Conditional Formatting.
To have it more flexible you would need to use the Worksheet_Change event.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
Thanks for your interest, but this is not what i need, if i will put a name from the second table, in sheet 3 the colors doesn't changing. I need the colors according the font color of table which is the name.
Also not since the first series will be always blue in color, as well as the same applies to the four colors.
Ok, guess you need some vba.
But before jumping into that I'd like to have a clear understanding of what you're saying.
- The tables in sheet 1 need to be colored according to the color of the supervisor of the employees in the table
- The supervisor's color is defined in the table in sheet 2
- Employees are assigned to supervisors in the table of sheet 3
- the tables in sheet 1 need to be filled with employees according to the assigned supervisor
So if for example B.Nick is assigned to a new supervisor, he will be moved to that supervisor's table in sheet 1
In the sheet 1, the tables have the desired colors. I chose surnames of all the tables and put in the sheet 2 mixed. (If you see the formulas in sheet 3 you understand). Then I have some results in sheet 3. In the sheet 3 in any rows there is a name from a table of the sheet 1, I want those details in row having the font color of the table that exists the lastname.
I update the first post with new wb with more details.
I sent a report to close this thread, i make a new with more details.
No need to start a new thread: just make a new post here with the updated details.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Still on it. Looking forward to more details.
Ok, I need, basis the surnames of third sheet, the font color to changes accordingly to whom table exists each name.
Example: In any row of area in sheet3 in cells of lastnames & names, there will be names of all the tables, scrambled.
in any line, each name that there should be colored with the colors present in the list of the first sheet.
ie. if a lastname from green table is in the first or second or third or fourth row in sheet3,
i need all details in same row to have the same font color from the table which exists the lastname ie from the green table.
*Im trying to reach the simplest explanation through translation, because my English is not so good, sorry..
Anyway, thanks all the people who working with my book.
I'll give it try again.
If language is the problem may be you should also post your problem in the Non-English section of this forum: http://www.excelforum.com/non-english-excel/
Here's my try.
When changing the value of a cell in sheet 2 in the ranges C3:C6 or F3:F6 the Worksheet_Change event is excuted.
This procedure looks up the value of the changed cell in the tables in sheet 1 and - when found - retrieves the fontcolor of the cell found in the table.
Then it looks up the value in the examples in sheet 3 and - if found - changes the fontcolor to the found color. The procedure also changes the fontcolor of the changed cell in sheet 2.
Hope this is anywhere near to what you're looking for.
Did need to change the table names as my Dutch VBA can't handle Greek characters.
Tsjallie,
There is no VBA code.
Ben Van Johnson
Hello, well the solution was found,
in the sheet 2, play by entering different names of the 4 tables, and figure out what i need,
after you can check the results each time in the sheet 3.
But there is a serious problem.
If I have two people with the same surname, either in the same or a different table, do not work as intended.
Is there another way to fix this? Or with vba code?
Thanks
Oh dear, wrong file uploaded. Need glasses
Here's the right one.
Thx for the tip Ben.
If you have to people with the same surname then Excel as well as VBA has no way of distinguishing between them just by that name.
But I assume that supervisors have an Employee Id too. And that should be unique.
So if you introduce that Id in your tables then you can lookup the right person by that ID.
Thank you very much for your help. I really aprecciate this. But try to put two same lastnames in one or two tables, then put the lastname in sheet2 and check the results in sheet3. This is a problem because if i have two same lastnames in one or two and more tables the results doesnt going right.
Ok they have an ID but in sheet2 i have cells only for the lastname. I cant have another cell for id of each person. Any idea?
I do have an idea, but that would need the introduction of a combobox and an extra table with supervisors and their id's.
But, given the apparent constraints you're dealing with, would that be an option for you?
Here's an example of what you could do (if it's within your constraints).
The story is on sheet 2.
Needed to export all the sheets to a new workbook though as there's something rotten in the file you uploaded making it behave weird.
Don't know what might have caused that.
Basically, i try to adjust your code to the actual book, but after something changes gives error.
Can i clean the real book from sensitive data and upload it to see it, so you tell me if this can be done in my real book?
That's not Google Translate. You're making progress!WTF???
That would be a good idea. Is it possible to leave some dummy data in it?Can i clean the real book from sensitive data and upload it to see it, so you tell me if this can be done in my real book?
He he. Yes, great progress! My knowledge at english language is very poor and many times i must use google translate. But google translation, is tragic..
Back to our topic now!
Ok, i will prepair the book with some dummy data to understand what i'm asking. But please, if you understand what i need, after you see the book dont scold me ok? Ha ha
Anyway, i must to thank you very very much from my heart for your help and your interest.
To answer your question:I think that is the perfect solution for what i want. Can you give more explanation about this?
If you select cell C3 then the event-procedure Worksheet_SelectionChange() is executed.
This procedure
- checks if the selected cell is C3 and if so
- Makes the combobox which is covering cell C3 visible and opens it
If you select a supervisor from the combobox then the event-procedure ComboBox1_Change() is executed.
This procedure
- moves the value of the first column of the combobox to cell C3
- Hides the combobox
- Selects the cell right to the left of C3
This is done to make sure that selecting C3 again the Worksheet_SelectionChange() is executed
When moving the value of the combobox to cell C3 the event-procedure Worksheet_Change() is executed.
This procedure
- checks if the changed cell is C3 and if so
- Looks up the supervisor in the table specified in the 3rd column of the combobox
- Picks up the font color of the found record
- Looks up the found supervisor in Example1 and set the fontcolor of the entire row in Example1 to the previously found font color
- Same for example2
This procedure uses a custom function FindPart(), because the value to search for in Example1 and Example2 is part of the value in the column to search.
Thanks for the explanation!
Ok here is the book with dummie data. I believe to explain good to understand what I need and then you can tell me if it is possible.
Thanks for your time.
I think it's got something to do with an incompatabilty between your Greek Office and my Dutch Office. Don't think my vba understands Greek characters. And neither do I
For my understanding to relate the example you posted to the workbook:Pls confirm if this is right.
- The tables in Sheet1 of the example relate to the table BLUE, RED, GREEN and BLACK in the workbook
- The two examples in Sheet3 of the example relate to the sheets PLAN1 and PLAN2 in the workbook
- The tables in Sheet2 of the example relate to the Sheets PLAN1 and PLAN2.
The first query is yes. The second, if you mean sheet 3 the sheet table organization is also yes. For the third query what do you mean sheet 2? Because I'm Confused little.
Do whatever changes you just want. except for the last three sheets, I am counting on your creativity!
They are for some titles.names are only in uncolored rows
Everywhere you see grey color is for some title.
Here's a more visual way of how I think the actual workbook is related to the initially posted example.
MappingExample2Workbook.JPG
But anyhow, I have an idea how to do what you need. Working on that.
As an update here's what I got so far.
In PLAN1 select one of the cells in a red box and see what happens.
Notice that this version is ONLY doing what you asked for PLAN1.
Still working on getting things done for TABLE OF ORGANIZATION.
The combobox is filled in Thisworkbook_Open().
Rest works like the workbook I posted earlier.
Good evening my friend, I believe you to be well. I want to thank you from my heart for the time spent on the book. Is appreciated your patience to understand what I need.
It is exactly what I want In sheet PLAN 1 as your example. One thing if it is easy to change, instead of the ID, to have the result of column POSITION.
Thank you again.
Very helpful post thanks for sharing with us.
Having great difficulty adding data to Table Of Organization, because it's structure is not clear.
What is the meaning of the separate data areas?
Can I make tables of the data areas? That would make it easier to find where to add the data.
Do I just fill the table from the top down? If so then what is the meaning of the grey rows where you put titles.
What titles would that be?
When the workbook is loaded the combobox in sheet "PLAN 1" is filled.
This is done by the procedure LoadSupervisorsList in mdlProcedures.
When in sheet "PLAN 1" a cell is selected then the Worksheet_SelectionChange event is triggered.
If the selected cell is in the range O6:O10 or J9:J12 then the event-procedure will
- give the value of the selected cell to the combobox
If that's an other value then the Combobox1_Change event is triggered
- make the combobox visible and open it
When the Combobox1_Change event is triggered the event-procedure will
- put the LastName of the selected item to the selected cell
If that's an other value then the Worksheet_Change event will be triggered
- hide the combobox
- select the cell to the left of the selected cell
When the Worksheet_Change event is triggered and the changed cell is in the range O6:O10 or J9:J12 then the event-procedure will
- put the Position in the cell to the left of the originally selected cell
- set the fontcolor of the row the selected cell is in to the color of the table the data is from
- Call the procedure FillTableOfOrganization (which is in mdlProcedures)
The procedure FillTableOfOrganization will
- select the area in the sheet which is in use
- filter out title rows and rows already having data
- put Specialty in column 5
- put Position in column 6
- put Age in column 7
- put LastName & Name in column 8
- put Father's Name in column 9
- put Id in column 10
- set the fontcolor of the row to the color of the table the data is from
Finally, if you make changes to any of the tables BLUE, RED, GREEN or BLACK the combobox will be updated.
However, data already copied to PLAN 1 and TABLE OF ORGANIZATION will not be updated.
Important note:
because the sheets PLAN 1 and TABLE OF ORGANIZATION are not well structured the procedures putting the data in these sheets may not be fully reliable.
Hello my friend Tsjallie, sorry I'm late to respond. I will rebuild from the beginning the book and will be written step by step exactly what I do in each ply. so as not to confuse and inconvenience. Thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks