+ Reply to Thread
Results 1 to 46 of 46

VBA code for changing font colors

  1. #1
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    VBA code for changing font colors

    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

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Coloring text according to the tables

    This example uses Conditional Formatting.
    To have it more flexible you would need to use the Worksheet_Change event.
    Attached Files Attached Files
    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 ...

  3. #3
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Coloring text according to the tables

    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.

  4. #4
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Coloring text according to the tables

    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

  5. #5
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Coloring text according to the tables

    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.

  6. #6
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Coloring text according to the tables

    I update the first post with new wb with more details.

  7. #7
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Coloring text according to the tables

    I sent a report to close this thread, i make a new with more details.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,407

    Re: Coloring text according to the tables

    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.

  9. #9
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Coloring text according to the tables

    Still on it. Looking forward to more details.

  10. #10
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Coloring text according to the tables

    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.
    Attached Files Attached Files

  11. #11
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Coloring text according to the tables

    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/

  12. #12
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Coloring text according to the tables

    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.
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Coloring text according to the tables

    Tsjallie,
    There is no VBA code.
    Ben Van Johnson

  14. #14
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Coloring text according to the tables

    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
    Attached Files Attached Files

  15. #15
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    Oh dear, wrong file uploaded. Need glasses
    Here's the right one.

    Thx for the tip Ben.
    Attached Files Attached Files

  16. #16
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    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.

  17. #17
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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.

  18. #18
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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?

  19. #19
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    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?

  20. #20
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    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.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Quote Originally Posted by Tsjallie View Post
    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.
    WTF??? how exactly did this the magic??... I think that is the perfect solution for what i want. Can you give more explanation about this?

  22. #22
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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?

  23. #23
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    WTF???
    That's not Google Translate. You're making progress!
    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 would be a good idea. Is it possible to leave some dummy data in it?

  24. #24
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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.

  25. #25
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    I think that is the perfect solution for what i want. Can you give more explanation about this?
    To answer your question:

    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.

  26. #26
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Thanks for the explanation!

  27. #27
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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.
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Quote Originally Posted by Tsjallie View Post
    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.
    He he..! Really I don't know what causes in wb.

  29. #29
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Quote Originally Posted by Tsjallie View Post
    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.
    Hee.! Really I don't know what causes in wb.

  30. #30
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    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

  31. #31
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Quote Originally Posted by Tsjallie View Post
    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
    You mentioned in your post #20? Or in the last file that I uploaded?

  32. #32
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    For my understanding to relate the example you posted to the workbook:
    • 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.
    Pls confirm if this is right.

  33. #33
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    Quote Originally Posted by Immortal2014 View Post
    You mentioned in your post #20? Or in the last file that I uploaded?
    Probably both. I think it's in the objectnames of the sheets.
    It's not a problem though.

  34. #34
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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.

  35. #35
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Quote Originally Posted by Tsjallie View Post
    Probably both. I think it's in the objectnames of the sheets.
    It's not a problem though.
    Its not a problem for me how tables named or any object, area or any other who needs name.

  36. #36
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Do whatever changes you just want. except for the last three sheets, I am counting on your creativity!

  37. #37
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    Quote Originally Posted by Immortal2014 View Post
    Do whatever changes you just want. except for the last three sheets, I am counting on your creativity!
    What are the grey rows in TABLE OF ORGANIZATION for. Can they go?

  38. #38
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    They are for some titles.names are only in uncolored rows

    Everywhere you see grey color is for some title.

  39. #39
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    Quote Originally Posted by Immortal2014 View Post
    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.
    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.

  40. #40
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    Quote Originally Posted by Tsjallie View Post
    Here's a more visual way of how I think the actual workbook is related to the initially posted example.
    Attachment 491775
    But anyhow, I have an idea how to do what you need. Working on that.
    Hello, yes exactly that. Plan 1 & 2 and table of organization must been as I have them in wb, sheets blue, red, green and black can designed as you want, also if you want you can have all 4 tables in one sheet, if this can help the situation.

  41. #41
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    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.
    Attached Files Attached Files

  42. #42
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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.

  43. #43
    Registered User
    Join Date
    11-25-2016
    Location
    United State
    MS-Off Ver
    7
    Posts
    1

    Re: Coloring text according to the tables

    Very helpful post thanks for sharing with us.

  44. #44
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    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?

  45. #45
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: VBA code for changing font colors

    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.
    Attached Files Attached Files

  46. #46
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: VBA code for changing font colors

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 10-21-2015, 03:51 PM
  2. Changing Tab Colors Via Code (Type Mismatch)
    By Warderbrad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2012, 09:56 PM
  3. Changing font colors in rows of match data
    By atltempleman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2012, 03:32 PM
  4. Replies: 2
    Last Post: 12-24-2011, 04:58 PM
  5. Conditional format for changing font colors using VBA
    By marc5354 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2010, 04:32 PM
  6. Assistance needed with case statement changing cell and font colors
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2009, 10:50 AM
  7. changing colors of font in functions
    By andreee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-02-2006, 08:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1