VBA Automatically run macro when cell value is changed via formula

Question

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

Better put some sample data, then output and explain logic to get that output. — Harun24hr
– Harun24hr, Commented Mar 27, 2018 at 4:46
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
– Tony M, Commented Mar 27, 2018 at 4:53
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
– AJD, Commented Mar 27, 2018 at 6:32
How about making use of the worksheet_change event. This gets triggered when either user input or VBA makes changes to the sheet. — Luuklag
– Luuklag, Commented Mar 27, 2018 at 7:26
@chrisneilsen well cpearson claims otherwise: cpearson.com/excel/events.aspx — Luuklag
– Luuklag, Commented Mar 27, 2018 at 12:21

user9540483 · Accepted Answer · 2018-03-27 13:15:03Z

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.

Collectives™ on Stack Overflow

VBA Automatically run macro when cell value is changed via formula

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related