Help: Auto colour cell based on input in another cell

[Sha]

Hi out there,

i am trying to get the col J (date) to change colour after a certain number of days if cell F (date) is empty. Below are my codes. Could anyone tell me what i am doing wrong. Please do bare with me as i am really new at VBA.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Cells.Value > 0 Then Exit Sub

   Set xRg = Intersect(Range("F1:F3000"), Target)

   If xRg Is Nothing Then Exit Sub

   If IsNumeric(Target.Value) And Target.Value = "" Then

   Call HighlightCells


    End If

End Sub

 


Sub HighlightCells()


Dim dtrg As Range: Set dtrg = Range("J1:J3000")

Dim dtCell As Range

For Each dtCell In dtrg.Cells

If dtCell.Value <> "" And dtCell.Value < Date - 40 Then _

    dtCell.Interior.ColorIndex = 3


'End If

Next dtCell

 

[Sha]

Hi Dan,

Yes that is correct. the range kept reading F3 and J3 rather than F2 and J2 which is the start of data. Could i also ask a question?

When implementing a Conditional formatting...it only applies to the active sheet right? is there any way that I could apply the condition once to the whole workbook, so that there is no need to repeat the process of implementing of CF for each worksheet?

 

[Dan_W]

I'm so sorry - This completely escaped my attention.
Unfortunately, conditional formatting will only apply to one worksheet. If you wanted to automatically replicate the CF across the entire workbook, you would need to use VBA. I don't think it would require much knowledge, though, to be honest. Have you ever used the Macro Recorder before? Basically, what it does is it watches what you do, and then generates VBA code for you, that you can assign to a button and then click to reproduce the same results on each worksheet. With a tiny bit of VBA, you could ever skip having to press the button for each worksheet! Is that something you'd like to try?