0

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

John Farm
  • 9
  • 3
  • 2
    Better put some sample data, then output and explain logic to get that output. – Harun24hr Mar 27 '18 at 04: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: https://stackoverflow.com/questions/49442039/copy-multiple-cell-colours-from-one-worksheet-to-another/49442338#49442338 https://stackoverflow.com/questions/49438998/worksheet-changebyval-target-as-range-does-not-change-the-cells-until-i-click/49439124#49439124 – Tony M Mar 27 '18 at 04:53
  • 1
    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 Mar 27 '18 at 06: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 Mar 27 '18 at 07:26
  • @chrisneilsen well cpearson claims otherwise: http://www.cpearson.com/excel/events.aspx – Luuklag Mar 27 '18 at 12:21
  • Possible duplicate of [VBA trigger macro on cell value change](https://stackoverflow.com/questions/18500608/vba-trigger-macro-on-cell-value-change) – Sirmyself Mar 27 '18 at 13:18
  • I neglected to mention that I have another macro in which when it runs, it inserts a new row at the top of the "outstanding tasks" list (Range B3:K3) and obviously pushes the old rows down. The thing with this is that conditional formatting will not apply to the new row. Only to the previously selected area. So I do not think conditional formatting will be the best idea. – John Farm Mar 27 '18 at 23:44

1 Answers1

0

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.

user9540483
  • 1
  • 1