Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    conditional formatting (more than 3 variables)

    I have the names of 10 people in a spreadsheet. I allocate tasks to them on certain days. I want each name to be a different colour. I don't want to have to run macros. Help please (newbie, first question).

  2. #2
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi New Lounger

    For Excel 2007>
    Assuming the names will be in column A for example.
    Select the cells that the rule needs to apply to. Home Tab > Conditional formatting > New Rule > Use a formula etc > =$A1="Fred" > Format as required. Carry out for each name.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts

    Employees with colors

    Each box has validated data with unique consistent colors for each of the names.

    HTH,
    Maud

    Conditions.jpg
    Attached Files Attached Files
    Last edited by Maudibe; 2013-01-25 at 17:14. Reason: rewording

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brewer,

    Ok, I know you said you didn't want to run macros. Given that and the fact that this macro runs itself you might find it interesting and maybe even a little easier to use once setup. The macro works using the Worksheet_Change event so it fires it self off everytime the worksheet where the code resides changes. It immediately makes a couple of checks so it doesn't run needlessly.
    1. If more than one cell is selected
    2. If the selected {read data entered/edited} cell isn't in column A. Of course it could be changed to be another column(s) if that is where the names are.

    So when you enter a name it will automatically check the master table (in the MasterFMT sheet) for a name match (currently set to be case insensitive but that too can be changed). If the name is found it then looks to see what color that name is formatted as and then applies it to the name just entered/edited. If no match is found it exits with no change. Need to add a new name just add it to the MasterFMT sheet and give it a color and you're done! It is a little easier than maintaining a long conditional format list.

    I've also included a routine that can be used if you decide to change the colors of names. Just make the changes in the MasterFMT sheet then highlight/select all the names in the data sheet press ALT+F8 (sorry you do have to RUN this one) and select ResetColors and click RUN. That's it.

    I hope you find this useful or if you don't maybe some one else will.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    @ Maudibe

    I have read the OP post 3 times now, Where does the data validation come in!!!!!
    And why post the same formula ="Name"!!

    @ brewer39
    If you have Excel 2003< you will have to go the VBA route as in Excel 2003 you can only have 3 conditions.
    Last edited by Kevin@Radstock; 2013-01-26 at 03:05.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Since Brewer didn't offer the way he had set up the sheet, I was just showing one of many ways to achieve a goal that the poster may not have considered. And who is to say he is not using data validation? Please look at it as reinforcing that your formula is correct. I was merely showing how to apply it. I think one of the 5 of your exclamations points should be a "?"

    Maud
    Last edited by Maudibe; 2013-01-26 at 12:36. Reason: misinterpretation

  7. #7
    New Lounger
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks all for the super quick replies. I do have the early excel so I am limited to 3 conditions. I cannot even open your attachments with this old excel.

  8. #8
    New Lounger
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maybe I do need a macro. After I add/change names I could run it to set cell colour to a number where the number is derived from a lookup table eg dave = 13 fred = 17 etc. If the macro was attached to a text box it would be ok -- The end user knows nothing about Excel and has asked me to help him, thanks again.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brewer,

    Here's the file in .xls format.
    BTW: you can download the Office Compatibility Pack from MS which will downward convert the new formats to the old 2003 formats.

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    New Lounger
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks retired geek - but when I change on of the names I get

    compile error variable not defined (x1themecolordark1 is highlighted)

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brewer,

    My BAD!

    I should have but didn't boot over to my Excel 2003 and test this. There are three lines in that section that are 2007+ specific and are not useable in 2003. I've commented them out and noted this in the code. It still works fine in 2003 with this slight adjustment.

    Sorry for the confusion.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    New Lounger
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you - it works fine. I like to learn more about excel and while I don't understand the commands in the macro, I figured out how to make more than column A respond, and checked I could add a new name to the list on the second sheet. So 2 supplementary questions - when I typed Bruce elsewhere in first worksheet it coloured correctly, but when I deleted that cell the colour remained, can I define a blank cell in the list of names as "having no fill colour". Second, if I create a third worksheet, and a fourth and so on (one for each month) can I apply the same formatting to them. I am 67, retired for 5 years, and try to keep my brain ticking over. I used to work with excel a lot.

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brewer,

    When you said Delete did you mean delete or clear contents {this is what the Delete Key does!}. If you delete via a Right-Click then Delete and choose shift left or entire row the color is gone. If you use the Delete Key or select Clear Contents from the right-click menu the color remains. Defining a Blank cell with no color will Not work because the code uses the blank cell to cease processing.

    If you add additional worksheets the code must be in each sheet module. The easiest way to accomplish this is to right-click on the Data sheet and make a copy then rename the sheet. The code will be copied along with the sheet.

    Learning is what this forum is all about so have at it and post back whenever you have a question all those here will be glad to help!

    BTW: The macro can be made to color more than just the name if you want it to color the entire data set in the row with the name. It is however designed to work on lists, so if you are placing names in more than one column deleting names will product the behavior you describe. That said if this is a requirement the macro could be modified to check for a blank value in the Target cell and delete the color.

    Update: I've attached a workbook that has a new sheet Data Updated. This worksheet has the macro changed so that if you delete a name {via the Delete Key or Clear Contents} it will return the color to blank. The original remains so you can compare and see what changes were made to accomplish this and aid your learning process. HTH
    Attached Files Attached Files
    Last edited by RetiredGeek; 2013-01-27 at 15:24.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    New Lounger
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks. Do you ever come to London? If so I wish to shake your hand and buy you a beer.

  15. #15
    New Lounger
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I extended range and put bruce in C10 and it coloured correct. I cleared contents - Bruce disappeared ok but cell remains yellow....

Page 1 of 2 12 LastLast

Posting Permissions

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