Combining 2 worksheet change events into 1

[Ironman]

Hi

The 2nd of the 2 worksheet change events below doesn't work (no VB error, it just doesn't run), but I can't see what I need to change/add to make both run.

If Target.Column = 4 And Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then
  
    Lr1 = Target.Row
  
    If Sheets("Training Log").Range("D" & Lr1).Value >= 0.0833 Then
        Lr2 = Sheets("Iron Man Log").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("Iron Man Log").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).Value  'date
        Sheets("Iron Man Log").Range("B" & Lr2).Value = Sheets("Training Log").Range("C" & Lr1).Value  'distance
        Sheets("Iron Man Log").Range("C" & Lr2).Value = Sheets("Training Log").Range("D" & Lr1).Value  'time
    End If
  
'MACRO 1 - COLUMN D
'Iron Man Bronze (2-3hrs)
    If Sheets("Training Log").Range("D" & Lr1).Value >= 0.0833 And Sheets("Training Log").Range("D" & Lr1).Value < 0.1249 Then
       Sheets("Training Log").Range("D" & Lr1).Resize(, 1).Interior.Color = RGB(255, 204, 153)
    End If
  
'Iron Man Silver (3-3.5hrs)
    If Sheets("Training Log").Range("D" & Lr1).Value >= 0.125 And Sheets("Training Log").Range("D" & Lr1).Value < 0.1458 Then
       Sheets("Training Log").Range("D" & Lr1).Resize(, 1).Interior.Color = RGB(191, 191, 191)
    End If
  
'Iron Man Gold (3.5hrs - 4hrs)
    If Sheets("Training Log").Range("D" & Lr1).Value >= 0.1459 And Sheets("Training Log").Range("D" & Lr1).Value < 0.1665 Then
       Sheets("Training Log").Range("D" & Lr1).Resize(, 1).Interior.Color = RGB(255, 204, 0)
    End If
  
'Iron Man Platinum (4hrs+)
    If Sheets("Training Log").Range("D" & Lr1).Value >= 0.1667 Then
       Sheets("Training Log").Range("D" & Lr1).Resize(, 1).Interior.Color = RGB(242, 242, 242)
    End If

End If

'MACRO 2 - COLUMN C
If Target.Column = 3 And Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then
  
    Lr3 = Target.Row

'Iron Man Bronze (9.9M - 13.1M)
    If Sheets("Training Log").Range("C" & Lr3).Value >= 9.9 And Sheets("Training Log").Range("C" & Lr3).Value < 13.2 Then
        Sheets("Training Log").Range("C" & Lr3).Interior.Color = RGB(255, 204, 153)
    End If
  
'Iron Man Silver (13.2M - 15.0M)
    If Sheets("Training Log").Range("C" & Lr3).Value >= 13.2 And Sheets("Training Log").Range("C" & Lr3).Value < 15.1 Then
        Sheets("Training Log").Range("C" & Lr3).Interior.Color = RGB(191, 191, 191)
    End If

'Iron Man Gold (15.1M - 16.9M)
    If Sheets("Training Log").Range("C" & Lr3).Value >= 15.1 And Sheets("Training Log").Range("C" & Lr3).Value < 17 Then
        Sheets("Training Log").Range("C" & Lr3).Interior.Color = RGB(255, 204, 0)
    End If

'Iron Man Platinum (17M+)
    If Sheets("Training Log").Range("C" & Lr3).Value >= 17 Then
        Sheets("Training Log").Range("C" & Lr3).Interior.Color = RGB(242, 242, 242)
    End If

End If

I'd be grateful if this can be modified, possibly combining the 2 macros into one, so they will both run.

Many thanks!

 

[Joe4]

Can you post a small sample of your data, and explain exactly what you are doing that should trigger this second block of code to run?

 

[Ironman]

Sure Joe - Cols C & D:

12.1	2:37:08
8.8	1:52:53
8.9	1:45:43
15.4	3:38:03

When the values fall within the parameters shown in my first post, the fill colours should change when I enter data in another col in the same row.

 

[Joe4]

OK, note this line:

If Target.Column = 3 And Target.Row = Range("A" & Rows.Count).End(xlUp).Row Then

That says you are watching for the following conditions to trigger it:
1. A manual change in column C
2. The row that is updated matches the last row in column A with data.

Is that really how you want that to work, or should it be checking for the last row in column C, not A?

 

[Ironman]

It should it be checking for the last row in column C. The code for Col D was written for me previously, so I thought if I just copied it and changed D to C to be consistent, it would work (so why should the code for Col D run OK when that references Col A as well?).

 

[Joe4]

It should it be checking for the last row in column C. The code for Col D was written for me previously, so I thought if I just copied it and changed D to C to be consistent, it would work (so why should the code for Col D run OK when that references Col A as well?).

If your last row with data in column D matches the last row with data in column A, it would work (since they are the same value). But if the aren't then it wouldn't work they way you want.
The same thing for column C and A.

So I think you probably want this to start the first block of code:

If Target.Column = 4 And Target.Row = Range("D" & Rows.Count).End(xlUp).Row Then

and this line to start your second block of code:
'MACRO 2 - COLUMN C

If Target.Column = 3 And Target.Row = Range("C" & Rows.Count).End(xlUp).Row Then

 

[Ironman]

That's brilliant Joe, works great now, thanks ever so much and for your explanation!

 

[Joe4]

You are welcome!
Glad to help.