Change back round color of a cell w/VB

[Sean]

I would like to change the color of cell (1,1) to red by
placing a 1 in in cell (1,2) I can't seem to figur it out
please help.

Thank you

Sean

 

[Tod]

You can use the worksheet change event, like this:

'If the value in A2 = 1, change cell color of A1 to Red
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
If Range("A2").Value = 1 Then
Range("A1").Interior.ColorIndex = 3
Else
Range("A1").Interior.ColorIndex = xlNone
End If
End If
End Sub

tod

 

[Guest]

I tried to cut and paste the code but it did not work.
any other sugestions??????

Thanks,

Sean

 

[Tod]

Make sure to cut and paste the code into the ThisWorkbook
module and not the worksheet module.

 

[Guest]

is there a way to write the code where it will work several cells? I am
using this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("F2") Then
If Range("F2").Value = 1 Then
Range("A2").Interior.ColorIndex = 6
ElseIf Range("F2").Value = 2 Then
Range("A2").Interior.ColorIndex = 7
ElseIf Range("F2").Value = 3 Then
Range("A2").Interior.ColorIndex = 8
Else
Range("A2").Interior.ColorIndex = x1none
End If
End If
End Sub

what I would like to do is have this for f2:f100 if you put a 1,2,3 in one
of those cells it will change the color of the cell in the A colum.

Thank you for all the help!

Sean

 

[Don Lloyd]

Hi Sean,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rw, Col
Rw = Target.Row: Col = Target.Column
If Rw < 2 Or Rw > 100 Then Exit Sub
If Col <> 6 Then Exit Sub
Select Case Target.Value
Case Is = 1
Cells(Rw, 1).Interior.ColorIndex = 6
Case Is = 2
Cells(Rw, 1).Interior.ColorIndex = 7
Case Is = 3
Cells(Rw, 1).Interior.ColorIndex = 8
Case Else
Cells(Rw, 1).Interior.ColorIndex = xlNone
End Select
End Sub

Regards,
Don