Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Data validation; conditional formats (Excel 2003)

    Good morning....I need some help with data validation/conditional formatting....in the attached sample, cells G7:G10 will color red, yellow, blue if I8 contains a or b or c....cond formatting only allows for 3 colors....I would like to have G7:G10 color red (with white text) if I8 exceeds zero, so that someone working with this s/sheet will have a 'red' flag in G7:G10 to alert them to the fact that I8 exceeds zero. I tried to use Data Validation (with a custom formula) but that doesn't do it.....any suggestions?
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data validation; conditional formats (Excel 2003)

    Where does data validation come into this? DV controls data entry, not cell formatting, so I'm not sure what you want.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data validation; conditional formats (Excel 2003)

    Validation doesn't control a cell's formatting.

    To apply "conditional formatting" with more than 3 conditions, you can use the Worksheet_Change event, see <post:=557,373>post 557,373</post:> for an example.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Data validation; conditional formats (Excel 20

    I'm not sure if it does...I was kind of hoping that if invalid data (eg: 2) were entered in I8, then a Warning (through use of Data Validation) would pop up in, for example, cell G8.......couldn't make that happen (probably b/c DV is not for formatting), so that's why I submitted the question......what I want is some way to change the color of G7:G10 if I8 exceeds zero.......and cond formatting only allows 3 'options' (all of which will be used)...do I have to use some VBA code, perhaps? (and if so, what would that be...?) Thanks.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Data validation; conditional formats (Excel 20

    Oh..OK..thank you Hans....I was replying to Rory as you were replying to my post...I will look at that post you mention....thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data validation; conditional formats (Excel 20

    You can use Data | Validation to prevent the user from entering an incorrect value in I8. Which values are allowed?

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data validation; conditional formats (Excel 20

    If you use Data Validation with the List option on I8 you can stop people entering anything other than a, b or c - is that what you wanted?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Data validation; conditional formats (Excel 20

    To Hans and Rory....I knew about the List option way of doing it.....I was hoping that I could use DV to have some sort of pop-up box appear, for example, in G8 if the value in I8 exceeded zero...people don't actually enter data in I8---I8 is used to indicate if there are any errors in P8:Z8......the data (a number) will appear if there is an error in P8:Z8.....but maybe I'll work with the suggestion that Hans gave me re: worksheet change event, applied to the cells where I want the color change to occur....ps: where can I find the numbers that correspond to the colors...are the numbers found in the color pallette?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data validation; conditional formats (Excel 20


  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data validation; conditional formats (Excel 20

    Validation doesn't work for cells that contain a formula, it only works for data entry by the user.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data validation; conditional formats (Excel 20

    Why not apply the conditional formatting to I8 or P8:Z8 instead of to G7:G10?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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