On May 29, 1:17*pm, Antonio wrote:
Thanks, JW, but the macro didn't work. It works if I type the values, but I
want them to be associated to an if function, that will attribute the values
1,2,3,4 or 5 based on different conditions.



"JW" wrote:
On May 28, 1:40 pm, JW wrote:
On May 28, 1:18 pm, Antonio wrote:

Would anyone have a VAb code for a macro that changes the color of a cell
according to 5 different rules? A2 would be black if A1=1, green if A1=2,
gray if A1=3, blue if A1=4 or gold if A1=5.
Thanks

Here's one way. *Right click the sheet tab where you want this to
happen and select View Code. *Paste the below code in there. *Change
the Range("A2:A50") to whatever range you want the condition to apply.

Private Sub Worksheet_Change(ByVal Target As Range)
* * Dim clr As Integer
* * If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
* * * * Select Case Target
* * * * * * Case 1
* * * * * * * * clr = 1
* * * * * * Case 2
* * * * * * * * clr = 4
* * * * * * Case 3
* * * * * * * * clr = 15
* * * * * * Case 4
* * * * * * * * clr = 41
* * * * * * Case 5
* * * * * * * * clr = 44
* * * * * * Case Else
* * * * * * * * clr = -4142
* * * * End Select
* * * * Target.Offset(0, 1).Interior.ColorIndex = clr
* * End If
End Sub

typo on my part.

Private Sub Worksheet_Change(ByVal Target As Range)
* * Dim clr As Integer
* * If Not Intersect(Target, Range("A2:A50")) Is Nothing Then
* * * * Select Case Target
* * * * * * Case 1
* * * * * * * * clr = 1
* * * * * * Case 2
* * * * * * * * clr = 4
* * * * * * Case 3
* * * * * * * * clr = 15
* * * * * * Case 4
* * * * * * * * clr = 41
* * * * * * Case 5
* * * * * * * * clr = 44
* * * * * * Case Else
* * * * * * * * clr = -4142
* * * * End Select
* * * * Target.Offset(0, 1).Interior.ColorIndex = clr
* * End If
End Sub- Hide quoted text -

- Show quoted text -

Use the Calculate event instead then

Private Sub Worksheet_Calculate()
Dim clr As Integer, r As Range
Dim CondRange As Range
Set CondRange = Range("A2:A50")
For Each r In CondRange
Select Case r.Value
Case 1
clr = 1
Case 2
clr = 4
Case 3
clr = 15
Case 4
clr = 41
Case 5
clr = 44
Case Else
clr = -4142
End Select
r.Offset(0, 1).Interior.ColorIndex = clr
Next r
End Sub