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]

You could just apply these conditions in conditional formatting, but if you have 2003 you can only use three conditions.

http://www.contextures.com/xlcondFormat01.html

Otherwise, here is an example worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intclr As Integer
    If Not Intersect(Target, Range("A:NB")) Is Nothing Then
        Select Case Target
            Case "T"
                intclr = 40
            Case "Y"
                intclr = 36
            Case "C"
                intclr = 35
            Case "S1" To "S8"
                intclr = 37
            Case "O"
                intclr = 43
            Case "L1" To "L8"
                intclr = 42
            Case Else
        End Select
            Target.Interior.ColorIndex = intclr
    End If
End Sub

 

[anandvarma]

you can also do with conditional formating with IF condition

 

[Dazzawm]

Sorry that does not do anything, I will have several columns and rows where these initials will be entered.

 

[jeffreybrown]

Sorry that does not do anything

Hmmm...what does not do anything?

If you're talking about the code, did you place it in the worksheet module?

 

[Dazzawm]

Hmmm...what does not do anything?

If you're talking about the code did you place it is the worksheet module?

Yes I did, and when I typed some of the letters somewhere in the sheet the cells remained white.

 

[Noz2k]

Maybe something like this?

Dim cl As Range
For Each cl In Sheets("Sheet1").Range("A1:AZ1000")
    If cl.Value = "HP" Then
    cl.Interior.Color =COLOUR1
ElseIf
cl.Value = "RD" Then
cl.Interior.Color =COLOUR2
    End If
    Next cl

 

[kpark91]

Could we see the code you have typed in?

and if it didn't work,
try going into the VBE and create the event for worksheet_change manually.

If you have already done that, make sure you put this at the end of your code

Application.ScreenUpdating = True

Similarly, you could put this in front of your code to 'speed' things up.

Application.ScreenUpdating = False

 

[jeffreybrown]

Maybe a silly question, but did you change the codes and then type those codes in or leave the code the way it is?

I just put the code in my worksheet module, typed in a T and got a yellow.

 

[Dazzawm]

None of these are working, I must be doing something wrong! Can we start again from scratch please.