Instant update of cell depending on value in other cell

[WatfordKev]

I want to do the following:

• Update a cell (number).
• Then compare against 2 others. If the input value is:
• lower than or the first comparator, then a new cell = "red".
• equal to or between the comparators, then a new cell = "amber".
• above the second comparator, then the new cell = "green".

So an example would be

A2 = 5
A3 = 10

If I enter a value of 4 into A1, then cell A4 = "red"
If I enter a value of 7 into A1, then cell A4 = "amber"
If I enter a value of 11 into A1, then cell A4 = "green"

The trick is, I want to to this in code, in a macro, and want this to happen as soon as I put the value in i.e. "on the fly". Is this possible?

And once I have the code, how do I 'attach' it to the workbook? Thanks!

 

[taurean]

Try the following worksheet event code by right-clicking on the specific sheet tab and then selecting view code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
    Select Case Target.Value2
    Case 4
    Range("A4").Interior.Color = vbRed
    Case 7
    Range("A4").Interior.Color = vbBlue
    Case 11
    Range("A4").Interior.Color = vbGreen
    Case Else
    Range("A4").Interior.Color = vbWhite
    End Select
Application.EnableEvents = True
End If
End Sub

I have used vb available colors you can use .ColorIndex for the specific color you want!

 

[Alpacino]

Hi,

You can use If then ElseIf structure to do this

Sub ColorCells()
If Range("A1").Value > 10 Then
Range("A4").interior.color = vbGreen
ElseIf Range("A1").value > 5 Then
Range("A4").interior.color = vbYellow
Elseif Range("A1").value > 0 Then
Range("A4").interior.color = Red
EndIf
End Sub

You can put this into the this worksheet
And select sheet change in top right corner

 

[WatfordKev]

Thanks guys this has set me on the way very well!