+ Reply to Thread
Results 1 to 14 of 14

Set background color based on cell value

  1. #1
    Registered User
    Join Date
    04-21-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    Thumbs up Set background color based on cell value

    Hi all,
    I have a workbook in which I want to set the background color based on a specific value in each individual cell within a specific range (not the entire sheet) and I have five values I want to use (1 to 5).
    I tried IF-Then-Else but didn't get the syntax right.

    Just to clarify, if I type 1in cell A1, I want the background to be red, if I type 2, I want the background to be green etc..
    I tried the following code with no luck.:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Selection.Value = 1 Then Selection.Interior.ColorIndex = 27 Else Selection.Interior.ColorIndex = xlNone
    If Selection.Value = 2 Then Selection.Interior.ColorIndex = 3 Else Selection.Interior.ColorIndex = xlNone
    If Selection.Value = 3 Then Selection.Interior.ColorIndex = 4 Else Selection.Interior.ColorIndex = xlNone
    If Selection.Value = 4 Then Selection.Interior.ColorIndex = 32 Else Selection.Interior.ColorIndex = xlNone
    If Selection.Value = 5 Then Selection.Interior.ColorIndex = 46 Else Selection.Interior.ColorIndex = xlNone

    End Sub
    Last edited by tgal; 04-29-2011 at 06:33 AM. Reason: Problem solved thanks to users Dom and snb

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Set background color based on cell value

    Hi,

    Have you tried using Conditional Formatting?

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    04-21-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Set background color based on cell value

    Quote Originally Posted by Domski View Post
    Hi,

    Have you tried using Conditional Formatting?

    Dom
    Hi, I have but my managers have the ability to **** up just about everything , that's why I want to use VBA.
    Thanks,
    Thomas

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Set background color based on cell value

    What about if they just decide not to enable macros? Trying to replicate what can already be done with standard Excel functionality is really just making life hard for yourself if you ask me.

    This would be how you would go about it if you decide to though:

    Please Login or Register  to view this content.

    The above example checks range A1:A10 so change as required. The code needs to go on the worksheet's code page.

    Dom

  5. #5
    Registered User
    Join Date
    04-21-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Set background color based on cell value

    Dom, works like a dream! Thanks a lot for your time and effort, it's highly appreciated.

    Another question, if I may. If I want to have the same formatting in another sheet with a linked formula to sheet1, like "=Sheet1!A1" in cell A1 in sheet2, is that possible or would it be easiest just to copy and paste formatting?

    (I plan to hide the sheets an unhide them with VBA, showing just a blank sheet saying "If you see thism pls reopen with macros enabled bla bla bla")

    Best regards

    Thomas
    Last edited by tgal; 04-28-2011 at 07:25 AM.

  6. #6
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Set background color based on cell value

    Hi,

    No worries. I guess you could do it like this:

    Please Login or Register  to view this content.

    Dom

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Set background color based on cell value

    or

    Please Login or Register  to view this content.
    Last edited by snb; 04-28-2011 at 08:09 AM.



  8. #8
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Set background color based on cell value

    snb,

    That avoids the use of select case (not sure if that's an advantage) but will bomb if the user changes more than one cell at once, i.e. copy...paste.

    Dom

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Set background color based on cell value

    It also avoids an unnecessary loop.
    To catch the copy/paste event:

    Please Login or Register  to view this content.
    Last edited by snb; 04-28-2011 at 10:09 AM.

  10. #10
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Set background color based on cell value

    Do you mean?

    Please Login or Register  to view this content.

    Why is the loop unnecessary if it allows for more than one cell to be changed at once? Avoiding it just seems like a step backwards to me unless there's a better way to deal with it that I've not thought of which is quite likely.

    Dom

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Set background color based on cell value

    The way I read the question: the user changes 1 cell at a time.

  12. #12
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Set background color based on cell value

    Quote Originally Posted by tgal View Post
    Hi, I have but my managers have the ability to **** up just about everything , that's why I want to use VBA.
    Thanks,
    Thomas
    From this I take it he wants to cover just about every eventuality

    I just don't see the point on limiting the code to working on one cell (at least without warning the user that they should only change one cell if they do change more) when a range can be handled quite easily.

    I used to limit change events to working on one cell but personally think this is a better approach.

    Dom
    Last edited by Domski; 04-28-2011 at 10:29 AM.

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Set background color based on cell value

    In that case I'd prefer:

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    04-21-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Set background color based on cell value

    To both of you, Dom and snb. Thanks for the assistance.

    Regards,

    Thomas

+ 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