Colouring Cells

[Dazzawm]

I have these initials below in these cells on the spreadsheet. How do I do it that when I enter any of these initials anywhere on the spreadsheet it colurs the cell in with the allocated colour?

Thanks

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:85px;" /><col style="width:114px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">HP</td><td style="background-color:#99cc00; font-weight:bold; text-align:left; ">RD</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#00ccff; font-weight:bold; text-align:left; ">SU</td><td style="background-color:#ff6600; font-weight:bold; text-align:left; ">MP</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffcc00; font-weight:bold; text-align:left; ">SP</td><td style="font-weight:bold; "> </td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;"

 

[jeffreybrown]

Sure were do you want to start.

Here's what I did...

Right click on sheet tab >> view code >> paste code on right side of screen from post #2

On that same spreadsheet here are the results. Notice I changed the code to columns("A:B") so when I entered some of those same values in column C no color.

Give us an idea of what you are trying?

Excel Workbook
	A	B	C
1	L7	L1	L7
2	U	T
3
4	Y		Y
5	B	S1	S5
Sheet5

Excel 2007

 

[Dazzawm]

I am doing as you say, my letters and colours start in a2 as my example, does the code you provided cater for where mine are?

 

[kpark91]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intclr As Integer
        Select Case Target.Value
            Case "HP"
                intclr = 40
            Case "RD"
                intclr = 36
            Case "SU"
                intclr = 35
            Case "MP"
                intclr = 37
            Case "SP"
                intclr = 43
        End Select
            Target.Interior.ColorIndex = intclr
    End If
End Sub

Please be informed that I didn't bother to change the colorindex according to the requirements.

You simply have to change the number to fit your needs.
You can visit this website: http://dmcritchie.mvps.org/excel/colors.htm
and find the color and find its colorindex and simply edit the code.

and run!

 

[Dazzawm]

I think I can set some conditional formatting rules as I have 2007, but I will send this to ppl that have 2003 downwards, so a code in the workbook would be best I think.

 

[Noz2k]

Sorry with mine "ElseIf cl.Value = "RD" Then" should all be 1 line, must have accidently hit enter when I copied it in. Otherwise both ways work for me though.

You have to amend the codes slightly though to fit your needs.

You need to set the range to match the cells which you want. As well as the initials and the colours to match those that you want.

 

[Dazzawm]

An error come up kpark91 - complie error 'end if without block if'

 

[Dazzawm]

Well I have sorted for me using conditional formatting but I am using 2007. I need to sort now for people I will be sending it to that use 97-2003, as you know only 3 conditions are allowed but I have 6.

 

[jeffreybrown]

Hi dazwm,

I can't access Box.net right now, but maybe in about 30 minutes I could upload a sample workbook if this would help you.

 

[Dazzawm]

Any help would be great, or PM me and I will give you my addy and you could send it straight to me.

 

[jeffreybrown]

Please send me a PM with your e-mail