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;"

 

[Dartagnan]

I am not sure which code you ended up using. But the following code appears to be working fine. Please let me know if I have the wrong code and what is the error.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intclr As Integer
If Application.Intersect(Range("A1"), Range("h15")) Is Nothing Then
If Target.count > 1 Then Exit Sub
Select Case Target
Case "T"
intclr = 40
Case "Y"
intclr = 36
Case "C"
intclr = 35
Case "S1" To "S8"
intclr = 37
Case "O" To "o"
intclr = 43
Case "L1" To "L8"
intclr = 42
Case Else
End Select
Target.Interior.ColorIndex = intclr
End If
End Sub
 
 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Long
Cancel = True
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("C" & Target.row), Cells(Target.row, Columns.count).End(xlToLeft))
For Each Dn In Rng
If Dn = "HP" Then c = c + 1
Next Dn
End If
MsgBox Target & " Has " & c & " Days Booked So Far This Year"
End Sub

[End Code]

 

[lincolnho]

Oh! I know how to do that now! I can answer someone's question, yay!

If you have excel 2010, you want to select all cells (or the range you want affected), then...

Home > Conditional Formatting > Highlight cell rules > Equal to > HP with Custom format > Fill > Yellow (on bg colour side) > Font > Bold > OK > OK. then repeat with the others

 

[Dazzawm]

I am using these 2 codes but it keeps debugging to the line highlighted in yellow

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intclr As Integer
If Application.Intersect(Range("A1"), Range("AA500")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Select Case Target
Case "T"
intclr = 40
Case "Y"
intclr = 36
Case "C"
intclr = 35
Case "S1" To "S8"
intclr = 37
Case "O" To "o"
intclr = 43
Case "L1" To "L8"
intclr = 42
Case Else
End Select
Target.Interior.ColorIndex = intclr
End Sub 
 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Long
Cancel = True
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("C" & Target.Row), Cells(Target.Row, Columns.Count).End(xlToLeft))
For Each Dn In Rng
If Dn = "HP" Then c = c + 1
Next Dn
End If
MsgBox Target & " Has " & c & " Days Booked So Far This Year"
End Sub

<!-- / message -->

 

[Dazzawm]

Oh! I know how to do that now! I can answer someone's question, yay!

If you have excel 2010, you want to select all cells (or the range you want affected), then...

Home > Conditional Formatting > Highlight cell rules > Equal to > HP with Custom format > Fill > Yellow (on bg colour side) > Font > Bold > OK > OK. then repeat with the others

Thanks I know how to do that but as stated earlier in the thread all users I send it to may not have such a new version of Excel.

 

[lincolnho]

sorry, I guess I didn't see that there were 4 pages to this thread

 

[Dartagnan]

Unless you put that msgbox within the IF statements it will always run.

 

[Dazzawm]

Unless you put that msgbox within the IF statements it will always run.

Could you do that for me please.

 

[Dartagnan]

Without really knowing the goal this is my best guess where the msgbox should be.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Long
Cancel = True
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("C" & Target.row), Cells(Target.row, Columns.count).End(xlToLeft))
For Each Dn In Rng
If Dn = "HP" Then c = c + 1
MsgBox Target & " Has " & c & " Days Booked So Far This Year"
Next Dn
End If

End Subl.

 

[Dazzawm]

Thanks that seems to have done it.

 

[Dartagnan]

No problem.