+ Reply to Thread
Results 1 to 10 of 10

Colour range of cells dependant on value of a cell VBA

  1. #1
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2003
    Posts
    129

    Colour range of cells dependant on value of a cell VBA

    Hi,

    I would like excel to colour in a range of cells a certain colour depending on the value of the cell.

    For example, if B2 = Yes, colour B2 to G2 Yellow. If B2 = No colour B2 to G2 Red. etc. (5+ conditions so conditional formatting isn't good enough)

    Additionally there are scenarios where I want [If b2 is Yes AND c2 is Yes then colour b2 to g2 blue, otherwise use the above conditions]

    This is what i have at the moment, it doesn't seem to work unless the values are number based, it doesn't do a range of cells, and I don't have the additional scenarios. Amateur hour.

    Please Login or Register  to view this content.
    Thanks in advance!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Colour range of cells dependant on value of a cell VBA

    UCase(Target.Value) ...

    Case "Yes" ... ?

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Colour range of cells dependant on value of a cell VBA

    Best to just ignore what I have attempted, I had no idea what I was doing and just tried to frankenstien something from examples found on google, clearly very poorly.

    If you do not have enough information to create what I need just ask for whatever additional information you need.

    Thanks
    Last edited by Cutter; 09-04-2012 at 12:46 PM. Reason: Removed whole post quote

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Colour range of cells dependant on value of a cell VBA

    What you've done looks fine, though I've not tested it.

    It's just that you're converting the Target Value to upper case, which is not a bad thing to avoid case sensitivity, but then you compare it to a mixed case text value "Yes". Doomed, ... doomed.

    Hence why numeric values work and text values don't.

    TMS

  5. #5
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Colour range of cells dependant on value of a cell VBA

    Oh haha, silly me. As I say, Frankenstein, didn't know the purpose of the "Ucase" but assumed it was essential.

    That's fixed now and "Yes" does indeed change the cell red. Woo!

    Now i just need to know:
    1). How to make it change a range of cells red in VBA
    2). How to do the more complex scenario in the original post [If b2 is Yes AND c2 is Yes then colour b2 to g2 blue, otherwise use the above conditions, which would make c2 to g2 red in this case.]

    Thanks
    Last edited by Cutter; 09-04-2012 at 12:46 PM. Reason: Removed whole post quote

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Colour range of cells dependant on value of a cell VBA

    Untested ...

    Please Login or Register  to view this content.

    Regards, TMS

  7. #7
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Colour range of cells dependant on value of a cell VBA

    Thanks TMS that's great.

    One problem I have is that we are using Column B for the answer but I would like A to J coloured. Obviously changing the 6 in your example, to 9, works for J. But both 0 and -1 gave me an error in my attempt to make it color column A. Know how to get around this?

    Also still have no idea how I would do the more complex scenarios above... Maybe just maybe I could use conditional formatting for those as I now have more available thanks to visual basic.

    Edit: Oh. And interestingly "'" doesn't seem to work, when entered it just goes to the else case. This is not a problem though as I am using drop down menus I can simply use else for these, as if it isn't the other options then it must be "'" (Which ironically is used as it appears like a blank option in the drop down menu.)
    Last edited by ThomasCarter; 09-05-2012 at 05:31 AM. Reason: Added info

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Colour range of cells dependant on value of a cell VBA

    Please Login or Register  to view this content.
    Regards, TMS

  9. #9
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Colour range of cells dependant on value of a cell VBA

    Thanks TMS, that works great.

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Colour range of cells dependant on value of a cell VBA

    @ ThomasCarter

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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