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

 

[Dan_W]

Could anyone tell me what i am doing wrong.

Has something gone wrong? What is it? Are you getting an error message and if so, what is the error number and error description?
Alternatively, if it's a matter of not getting the expected result, can you please give an example of the expected result and the actual result.

Also, as a separate point, have you considered using conditional formatting? This is the sort of thing it was designed to do.

 

[Sha]

Has something gone wrong? What is it? Are you getting an error message and if so, what is the error number and error description?
Alternatively, if it's a matter of not getting the expected result, can you please give an example of the expected result and the actual result.

Also, as a separate point, have you considered using conditional formatting? This is the sort of thing it was designed to do.

HI there,

I have redone the code as below but i get an error message saying "Can't execute code in break mode".
i am trying to highlight any cells in col J if its more than 40 days and if col f is empty.

example: Cell E3 is empty and Cell J3's date is expired by 40 days. Cell J3 is highlighted the affective cell.

'Cell will be highlighted if more than 40 days from today

Sub HighlightCells()


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

Dim CodeDate As Range
Set CodeDate = Range("F1:F3000")


Dim DisCharCell As Range
Dim CodeDateRange As Range


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

    DisCharCell.Interior.ColorIndex = 3

End If

Next dtCell


End Sub

 

[Dan_W]

Hi. So the error message - "Can't execute code in break mode" - occurs when the VB Editor is in break or 'pause' mode. You need to finish or stop whatever process you're running, and try running this again.

< Break mode

 

[Sha]

Thanks for the explanation. In your previous comment you mentioned about a conditional formatting. it means the conditional formatting function in Excel right, not VBA? Apologies for being a newbs. If it is by VBA would you be able to assist to guide me on how it works?

Sub HighlightCells()


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

Dim CodeDate As Range
Set CodeDate = Range("F1:F3000")


Dim DisCharCell As Range
Dim CodeDateRange As Range


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

    DisCharCell.Interior.ColorIndex = 3

End If

Next dtCell


End Sub

 

[Dan_W]

Hi - I meant something like the following. So according to your original post:

the col J (date) to change colour after a certain number of days if cell F (date) is empty.

• In this example below, Column F has the date that the report (or whatever) was submitted. If it wasn't submitted, the cell is blank.
• Column J is the actual due date.
• So in my example, "a certain number of days" is 7 days from today.

So if the report is overdue by more than a week as of today, then column J is highlighted with conditional formatting. .Look at rows 15 and 16 - in row 15, the due date was 18 October, which was less than a week ago, so the entry isn't highlighted. Whereas in Row 16, the deadline was over a week ago, and so this is highlighted.

Is that the sort of thing you were after?

MrExcel.xlsm
	F	G	H	I	J
1	Final Assessment				Due Date
2	07/09/2021				10/10/2021
3					02/10/2021
4	27/09/2021				10/10/2021
5					14/10/2021
6	07/10/2021				10/10/2021
7					10/10/2021
8	09/10/2021				10/10/2021
9					10/10/2021
10	27/09/2021				10/10/2021
11	04/10/2021				10/10/2021
12					12/10/2021
13	20/09/2021				12/10/2021
14	15/10/2021				18/10/2021
15					18/10/2021
16					10/10/2021
17	27/09/2021				10/10/2021
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J17	Expression	=AND(ISBLANK(F2),(TODAY()-7)>J2)	text	NO

 

[Sha]

HI Dan,

Yes. this was the result i was hoping to get. So this conditional formatting is in VBA?

 

[Sha]

Hi Dan,

I have tried using the conditional formatting in Excel but it doesn't seem to highlight the correct cells in col J (discharge date). i have applied the formula to the last col to see the true and false. Those with true should have the dates under discharge date highlighted in red, however, in the image below only 1 of the highlighted cells is correct. Why is that so? Both col F (date coded) and Col J (discharge date) have been formatted to date. Could it be possible its due to the fact that i am using Excel 2013?

formula used if : =AND(ISBLANK($F$F),(Today())-40>$J$J)

 

[Sha]

Hi Dan,

Do Ignore the past 2 posts. I figured it out! Thank you so much for your help! Really Really appreciate it!

 

[Dan_W]

You're very welcome. We'll done on working it out. For the benefit of anyone else with a similar problem to you, and who need to solve it, could you maybe explain what the solution was? Looking at your screen captures, I'd say that this problem usually occurs when there is a mismatch to between the cell range that you're applying the CF to and the cell range set out in the formula. Is that right?