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

[shafz]

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!

 

[Ian]

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

 

[shafz]

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

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

 

[Ian]

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