Help: Code for changing cell colour depending on cell contents

[philipwaldram]

Hi
I'm trying to change the colour of a cell to red, yellow or green depending on the number inside that cell. If it has a 1 it needs to be red, 2 should be yellow and 3 green. I'm running the code when the sheet is activated. I have been able to write code which lets me do it but there are 32 items in each of 5 rows so the procedure ends up being too large.
I think I am going to need a loop but haven't been able to work out how to perform the code on a range of cells.

So far I've got:

Range("g1").Select
ActiveCell.FormulaR1C1 = Research1 'adds the variable number to the cell
If Range("g1") = 1 Then
Selection.Font.ColorIndex = 3
Selection.Interior.ColorIndex = 3
ElseIf Range("g1") = 2 Then
Selection.Font.ColorIndex = 6
Selection.Interior.ColorIndex = 6
ElseIf Range("g1") = 3 Then
Selection.Font.ColorIndex = 4
Selection.Interior.ColorIndex = 4
Else
Selection.Font.ColorIndex = 39
Selection.Interior.ColorIndex = 39
End If

Please help it's driving me crazy

Thanks,
Phil

 

[chergh]

Try using the sheet change event like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Target.Value

Case 1
Target.Interior.ColorIndex = 3

Case 2
Target.Interior.ColorIndex = 6

Case 3
Target.Interior.ColorIndex = 4

Case Else

Target.Interior.ColorIndex = 39

End Select



End Sub

 

[philipwaldram]

That sounds great. If the range is g5 to g67 where would I add that to the code?
Thanks
Phil

 

[chergh]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 7 And _
   Target.Row >= 5 And _
   Target.Row <= 67 Then

Select Case Target.Value

Case 1
Target.Interior.ColorIndex = 3

Case 2
Target.Interior.ColorIndex = 6

Case 3
Target.Interior.ColorIndex = 4

Case Else

Target.Interior.ColorIndex = 39

End Select

End If

End Sub

 

[philipwaldram]

I don't know if I'm doing something wrong but this code won't do anything!

This is exactly what I've got:

Private Sub Worksheet_Activate(ByVal Sh As Object, ByVal Target As Range)
Select Case Target.Value
Case 1
Target.Interior.ColorIndex = 3
Case 2
Target.Interior.ColorIndex = 6
Case 3
Target.Interior.ColorIndex = 4
Case Else
Target.Interior.ColorIndex = 39
End Select
End If
End Sub

Am I going something really stupid?
Phil

 

[Brianwarnock]

Well you are using the activate rather than the change event as Chergh posted, however that is not the only problem, as i understand your first post you want to change a range of cells based on a value in a cell elsewhere, is that correct?
I also wouldn't make the font and interior colours the same.

You asked about changing a range of cells
Range("g5:g8").Interior.ColorIndex = 6

before i do more i think that I would like a proper clear description of what you are wishing to do.

Brian

 

[Brianwarnock]

I lied

The simplest code if my suspicions on your requirements are correct would be like

Sub clr()
Dim clrcode As Integer

 Select Case Range("A1").Value
 
 Case 1
 clrcode = 3
 Case 2
 clrcode = 6
 Case 3
 clrcode = 4
 Case Else
 clrcode = 39
 End Select
 
 Range("G5:G12").Interior.ColorIndex = clrcode
 

End Sub

Obviously you need to tailor this and you can still use the change event by marrying Chergh's example .
Note Target is the cell or cells being changed as per help ie where your value is.

Brian