Assign a color to the cell
 

I would like to get colors to the different cell whenever I enter a
specific number. Such as all 1=Yellow and I tried conditional
formatting but I need at least 12 number and color. Can I do it with
VBA?

Assign a color to the cell
 

Hi

You can try the following you just have to repeat the statement and
replace the X with the number you want
and the XX with the colorindex you need for the number.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = 1 Then
Target.Interior.ColorIndex = XX

ElseIf Target = X Then
Target.Interior.ColorIndex = XX
' repeat the above two lines as many times as needed

End If

End Sub

I hope this helps

Christian

Assign a color to the cell
 

Hi Tr2USA,

Try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rng2 As Range
Dim rcell As Range

Set rng = Me.Range("A1:A10") '<<==== CHANGE
Set rng2 = Intersect(rng, Target)

If Not rng2 Is Nothing Then
For Each rcell In rng2.Cells
With rcell
Select Case .Value
Case 1: .Interior.ColorIndex = 3
Case 2: .Interior.ColorIndex = 4
Case 3: .Interior.ColorIndex = 5
Case 4: .Interior.ColorIndex = 6
Case 5: .Interior.ColorIndex = 7
Case 6: .Interior.ColorIndex = 8
Case 7: .Interior.ColorIndex = 9
Case 8: .Interior.ColorIndex = 10
Case 9: .Interior.ColorIndex = 11
Case 10: .Interior.ColorIndex = 12
Case 11: .Interior.ColorIndex = 13
Case 12: .Interior.ColorIndex = 14
Case Else: .Interior.ColorIndex = xlNone
End Select
End With
Next rcell
End If

End Sub
'<<=============

Change the address of the rng variable to accord with the range of interest
and amend the ColorIndex values to taste.

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


wrote in message
oups.com...
I would like to get colors to the different cell whenever I enter a
specific number. Such as all 1=Yellow and I tried conditional
formatting but I need at least 12 number and color. Can I do it with
VBA?

Assign a color to the cell
 

This is great work. Thanks.

Assign a color to the cell
 

Thank you for your prompt answer. Very helpful.