End IF-VBA Conditional Formatting

[lumahai]

Hello,

Just looking to expand on something I have in the works.

Background:
Trying to have it where a certain cell will change colors depending if a date is changed in a certain cell.

Example: if either dates in cells E16 or F16 change, I would like for the date changed to highlight in color Along with cell J17 to change a certain color(any color at this point).

Reason is so I can show that when you changed this date(highlighted) that it affects this cell(J17).

I would like to expand this for multiple cells, which is where im having trouble. Current Macro below. The End If is erroring out and I think that has something to do with the additional cells that I want to check (E4, F4, color J5).

Also the color would be different for each look, so E16, F16, J17=Yellow. E4, F4, J5=red

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim cell As Range
' See if a cell was updated in E16 or F16
Set myRange = Intersect(Target, Range("E16:F16"))
If myRange Is Nothing Then Exit Sub

' Highlight changed cell(s)
For Each cell In myRange
cell.Interior.Color = 65535
Next cell

' Highlight cell J17
Range("J17").Interior.Color = 65535

End If
' See if a cell was updated in E4 or F4
Set myRange = Intersect(Target, Range("E4:F4"))
If myRange Is Nothing Then Exit Sub

' Highlight changed cell(s)
For Each cell In myRange
cell.Interior.Color = 65535
Next cell

' Highlight cell J5
Range("J5").Interior.Color = 65535
End Sub

 

[JackDanIce]

As a start, try:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng     As Range
    Dim x       As Long

    x = Cells(Rows.Count, 5).End(xlUp).row

    Set rng = Intersect(Target, Cells(1, 5).Resize(x, 2))
    If rng Is Nothing Or Target.Count > 1 Then Exit Sub

    'Remove below line if not needed
    Cells.Interior.Color = xlNone

    With Target
        .Interior.Color = 65535
        .Offset(1, 5).Interior.Color = 65535
    End With

End Sub

Creating a random colour to highlight can be done separately: Random Vba Color Generator And Color Reference Chart

 

[lumahai]

Thanks for your time and feedback!
If this portion of the code is by itself it works perfectly. It is just when I adapt and continue to want other cells to adapt if anything is changed within that cell.

The End If seems to be causing issue and shows a 'End If without block if' error. trying to figure out how to add on to the first portion to continually look at cells if they have changed, if so then change a correlating cell to a color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim cell As Range
' See if a cell was updated in E16 or F16
Set myRange = Intersect(Target, Range("E16:F16"))
If myRange Is Nothing Then Exit Sub

' Highlight changed cell(s)
For Each cell In myRange
cell.Interior.Color = 65535
Next cell

' Highlight cell J17
Range("J17").Interior.Color = 65535