Flag a row based on cell values over time

[Ludwigpedro]

Hi everyone,

Is it possible to "flag" a row everytime a a cell in it has a specific value or had the same specific value once? For exemple, I need to flag row 2 if cell B2=1 or B2 is 1 then gets changed to 0 (or other value). Also, values in B2 are set by a formula, not manually by the user.

 

[footoo]

Private Sub Worksheet_Calculate()
If [B2] = 1 Then [B2].Interior.Color = RGB(0, 255, 0)
End Sub

 

[Ludwigpedro]

Good, but I need the code to check all cells in column B, not only cell B2. Also, I would like to flag the row with another cell, let's say on column A, so that if B2=1 or was ever 1, then A2=1. If B2 changes from 1 to 1, A2 still needs to be = 1. Is that possible?

 

[Ludwigpedro]

Good, but I need the code to check all cells in column B, not only cell B2. Also, I would like to flag the row with another cell, let's say on column A, so that if B2=1 or was ever 1, then A2=1. If B2 changes from 1 to 1, A2 still needs to be = 1. Is that possible?

Sorry, I couldn't edit the previous post, but I made a mistake that needs correction.

if B2=1 or was ever 1, then A2=1. If B2 changes from 1 to 0, A2 still needs to be = 1. Is that possible?

 

[Ludwigpedro]

Uptade:

I got the code I need to work on a Specific Cell, but I need the code to check on all cells in Column O, not only cell O8, and update to the corresponding cell in Column A. Also, if there's a way to make an optimization to this, I would love to hear from you.

Private Sub Worksheet_Calculate()
If Sheets("Sheet1").Range("O8").Value = "Paralyzed" Then
Sheets("Sheet1").Range("A8").Value = 1
Else
If Sheets("Sheet1").Range("O8").Value = "Cancelled" Then
Sheets("Sheet1").Range("A8").Value = 1
End If
End If
End Sub

 

[footoo]

See whether this does what you want :

Private Sub Worksheet_Calculate()
Dim rng1$, rng2$
rng1 = Range([O2], Cells(Rows.Count, "O").End(xlUp)).Address
rng2 = Range(rng1).Offset(0, -14).Address
Range(rng2) = Evaluate("IF(" & rng1 & "=""Paralyzed"",1," & rng2 & ")")
Range(rng2) = Evaluate("IF(" & rng1 & "=""Cancelled"",1," & rng2 & ")")
End Sub

Or this (might be slower) :

Private Sub Worksheet_Calculate()
Dim rng As Range, cel As Range
Set rng = Range([O2], Cells(Rows.Count, "O").End(xlUp))
For Each cel In rng
    If cel = "Paralyzed" Or cel = "Cancelled" Then cel(1, -13) = 1
Next
End Sub