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

Discussion in 'General Software' started by shafz, Feb 4, 2010.

  1. shafz

    shafz

    Joined:
    Feb 4, 2010
    Messages:
    2
    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
    Messages:
    16,879
    Likes Received:
    73
    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
    Messages:
    2
    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
    Messages:
    16,879
    Likes Received:
    73
    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. AAAGH

    Cell background colour in Excel

    AAAGH, Oct 14, 2010, in forum: General Software
    Replies:
    0
    Views:
    830
    AAAGH
    Oct 14, 2010
  2. radselnam

    Incrementing a cell based on another cell's value

    radselnam, Jan 4, 2011, in forum: General Software
    Replies:
    0
    Views:
    599
    radselnam
    Jan 4, 2011
  3. gfa tracey

    Excel - Change cell colour based on text

    gfa tracey, Mar 16, 2011, in forum: General Software
    Replies:
    2
    Views:
    976
    gfa tracey
    Mar 17, 2011
  4. mjkd
    Replies:
    2
    Views:
    455
  5. wlandymore
    Replies:
    2
    Views:
    647
    wlandymore
    Feb 20, 2013
Loading...

Share This Page