VBA Automatically run macro when cell value is changed via formula

0
votes

I need help in using a macro for my workbook. Basically I have rows that gets automatically added by another macro. Every time a new row is added, The corresponding column has a True/False if formula. When the "False" result of the cell automatically updates to "True", I want the same row to be highlighted in a color and only this row. To further explain,

  • Range("B3:K3") is the row I want highlighted when cell ("O3") data changes to "True"
  • I have multiple rows that I need this macro to automatically run when the specific cell of column "O" turns from False to True via If formula
    • I also have another macro that adds an additional row to the list, so I cannot use conditional formatting

So sometimes I want Range("B19:K19") to be highlighted when cell ("O19") automatically changes. A few things to keep in mind

  • I am using an "auto refresh time clock" that basically links to cell ("S11")
  • In other words, the cells in Column O is saying (If the corresponding cell from column G [This is the date the task was entered] is over 24 hours old compared to the current time (cell "S11") then change value from False to True.

My only problem is, I cannot seem to get a working macro where it will highlight the row [Range(B3:K3)] of where the False/True data originates [corresponding cells on column O].

Can anyone help me with this?

EDIT Here is what I am trying to accomplish, but cannot get it to work.

Private Sub Worksheet_change(ByVal Target As Range) 
Dim Cl As Range
Dim R As Long
Set Cl = TargetAddress
R = Cl.Row

If Target.Address(, "O") = True Then
Target.Range("B" & R, Range("K" & R)).Interior.ColorIndex = 10
Else: Range("B5:K5").Interior.ColorIndex = 1
End If

End Sub

1
vbaexcel
Better put some sample data, then output and explain logic to get that output. – Harun24HR
Have you looked into using Target Cell with Worksheet_change? or is it that you don't know how to change colors? Here are 2 recent posts on those subjects, but you could search for others too. Then, as harun24h said, providing sample data would help: stackoverflow.com/questions/49442039/… stackoverflow.com/questions/49438998/… – Tony M
Conditional formatting? Select the row and conditionally format based on a formula. If this interests you John, let me know and I will write up a fuller answer. – AJD
How about making use of the worksheet_change event. This gets triggered when either user input or VBA makes changes to the sheet. – Luuklag
@chrisneilsen well cpearson claims otherwise: cpearson.com/excel/events.aspx – Luuklag

1 Answers

0
votes

I think condition formatting can do what you are requiring.

To make it simple, I assume there are only 3 column and 10 rows in your worksheet. Column A is the column your want to be highlighted and column C is the column that has a True/False value. First, select a1:a10, click condition formatting, and then click new rule. Click “Use a formula to determine which cells to format” in next window, and type =C1=True in the formula field. Click the Format bottom on lower right corner to select the color you need, click ok to finish the job.