How to change cell colour in Excel based on number *or* letter?!?

Excel Discussion in 'Microsoft Office' started by shafz, Feb 4, 2010.

  1. shafz

    shafz

    Joined:
    Feb 4, 2010
    Likes Received:
    0
    Hi all,

    Complete newbie here, have been reading various posts and trying to figure things out on my own but to no avail. Maybe someone can shed some light on my problem.

    I'm using Excel 2004 (Mac) and want to create a VBA/macro that would change the background colour of specific cells (say A4, A5, A6 and C8) based on the number OR letter in the cell. There is a drop-down from which the user must select either 0,1,2,3,4,5 or H for these cells. I want the colour to change based on the user's selection as follows:

    0 or 1 = Red (i.e. colour value 3)
    2 or 3 = Yellow (i.e. colour value 6)
    4 or 5 = Green (i.e. colour value 4)
    H = Lavender (i.e. colour value 7)

    I've found sample event code showing how to change the cell colour based on a numerical value/range, as well as different sample code showing how to change the colour based on the letter/text, but not *both in one*. (I've found out how to to insert the code via View Code when clicking the worksheet tab but am unfortunately not savvy enough to know how to set up the code correctly).

    I can't imagine it would be too complex for someone with a decent level of VBA/macro knowledge. BTW, I would be using conditional formatting to do this except it only allows for 3 conditions/colours, whereas I need 4! (thanks MicroSoft...)

    Any suggestions on how to do this would be very much appreciated!
     
    shafz, Feb 4, 2010
    #1
    1. Advertisements

  2. shafz

    Ian Administrator

    Joined:
    Feb 23, 2002
    Likes Received:
    323
    Technically, you can still use conditional formatting in this case - you have 4 cases and 3 max options. So just make the default background Lavender, then then use the 3 conditional cases to change it to Red/Yellow/Green. As it's a dropdown, this shouldn't break anything and selecting H will leave the default lavender colour. Not ideal, but perhaps a simple macro-free option.

    If you want to use VBA then you can use code like this:

    http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

    This page explains how the Case statement works for number and letter selections (near the bottom):

    http://www.ozgrid.com/VBA/select-case.htm

    Hope that helps :)
     
    Ian, Feb 5, 2010
    #2
    1. Advertisements

  3. shafz

    shafz

    Joined:
    Feb 4, 2010
    Likes Received:
    0
    Ian,

    Thanks for your thoughtful input. I really like the suggestion of setting the default colour to lavender. I had not considered that idea and I think I am leaning toward using that.

    For my own understanding though, how would I best modify the sample code you pointed me to? I've copied the code that seems most applicable below. However, this code changes the value in B1 based on the value in A1. As explained previously, what I would like to do is change the cell colour based on the cell value (number or letter). So e.g. if A1 = 1, the colour of A1 should change to red (VBA colour 3), if A1 = H, the colour of A1 should change to lavender (VBA colour 26).

    And do I just enter this code into my spreadsheet as is, or are some additional opening/closing code lines needed? I tried pasting this code in the View Code window, but it didn't seem to work.

    Many thanks!

    ---------------------

    Sub TheSelectCase()

    Select Case Range("A1").Value

    Case 100 To 500, 652, 700 To 1000, 1233, 1500 To 2000, "dog", "cat"

    Range("B1").Value = Range("A1").Value

    Case Else

    Range("B1").Value = 0

    End Select

    End Sub

    -----------------------
     
    shafz, Feb 8, 2010
    #3
  4. shafz

    Ian Administrator

    Joined:
    Feb 23, 2002
    Likes Received:
    323
    You'd need to enter this code in to Excel using VBA, but I'm not an expert on how to do that :blush:

    I think the method of just using a default colour will work best :D
     
    Ian, Feb 8, 2010
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sumo
    Replies:
    1
    Views:
    495
    PapaBear56
    Jul 20, 2005
  2. vackert58

    Excel Excel Inserting picture based on Drop Down

    vackert58, Jul 14, 2006, in forum: Microsoft Office
    Replies:
    0
    Views:
    307
    vackert58
    Jul 14, 2006
  3. MadeleineBuchberger

    Excel Excel VB - Copy only the colour format from columns

    MadeleineBuchberger, Oct 17, 2007, in forum: Microsoft Office
    Replies:
    0
    Views:
    435
    MadeleineBuchberger
    Oct 17, 2007
  4. Chuck101
    Replies:
    0
    Views:
    513
    Chuck101
    Mar 3, 2008
  5. Trish

    Excel Excel - Automatic Cell Colour Change 'traffic lights'

    Trish, Apr 23, 2008, in forum: Microsoft Office
    Replies:
    3
    Views:
    1,530
    Trish
    Apr 23, 2008
  6. GaryAus

    Excel Excel Cell colour changing

    GaryAus, Jul 1, 2008, in forum: Microsoft Office
    Replies:
    2
    Views:
    485
    GaryAus
    Jul 8, 2008
  7. SDennis
    Replies:
    1
    Views:
    1,665
    Anon_F
    Jul 17, 2008
  8. grafilo

    Excel Launch Excel, Create Graph based on Query

    grafilo, Jul 8, 2009, in forum: Microsoft Office
    Replies:
    0
    Views:
    756
    grafilo
    Jul 8, 2009
Loading...