PC Review


Reply
Thread Tools Rate Thread

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

 
 
New Member
Join Date: Feb 2010
Posts: 2
 
      5th Feb 2010
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!
 
Reply With Quote
 
 
 
 
Ian Ian is offline
Rocket Scientist
Ian's Avatar
Join Date: Feb 2002
Location: Manchester, UK
Posts: 16,921
 
      5th Feb 2010
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-cond...ting-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
 
Reply With Quote
 
 
 
 
New Member
Join Date: Feb 2010
Posts: 2
 
      8th Feb 2010
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

-----------------------
 
Reply With Quote
 
Ian Ian is offline
Rocket Scientist
Ian's Avatar
Join Date: Feb 2002
Location: Manchester, UK
Posts: 16,921
 
      8th Feb 2010
You'd need to enter this code in to Excel using VBA, but I'm not an expert on how to do that

I think the method of just using a default colour will work best
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
if cell colour is then change cell colour in range =?Utf-8?B?dGlnZXI=?= Microsoft Excel Programming 2 30th May 2007 05:32 AM
change a cell background colour to my own RGB colour requirements =?Utf-8?B?U3RlcGhlbiBEb3VnaHR5?= Microsoft Excel Misc 4 16th Jun 2006 01:08 PM
How to change the colour of a cell(s) based on cell value Artful Dodger Microsoft Excel Discussion 6 26th Nov 2004 10:34 PM
Need a cell to change colour based upon a word in another cell =?Utf-8?B?Y29va2ll?= Microsoft Excel Misc 4 11th Nov 2004 06:50 PM
Formatting cell colour based on colour of other cell =?Utf-8?B?TWFydGE=?= Microsoft Excel Worksheet Functions 2 16th Jun 2004 05:27 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:19 AM.