Belonging Row is not clear in Code

[Julian Schubert]

Hi @ all,

I have the following Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K:K")) Is _
    Nothing Then Exit Sub

Dim myRange As Range
Dim rngZelle As Range
Dim k As Range
Dim strComment As String
Dim Tag As String
Dim LRow As Long

Loeschen

Set myRange = Sheets("Sheet1").Range("A4:NB4")

With Sheets("Sheet2")
    LRow = .Cells(.Rows.Count, 26).End(xlUp).Row
End With

For Each k In Range("K3:K" & LRow)
For Each rngZelle In myRange

With rngZelle
    If .Value <= k And _
        .Value >= k.Offset(0, -1) Then
        .Interior.ColorIndex = 3
        .Font.ColorIndex = 3
    End If
    If .Value = k Then
        If Not .Comment Is Nothing Then
            .Comment.Delete
        End If
        strComment = k.Offset(0, -6)
        .AddComment strComment
        .Comment.Visible = True
        .Comment.Shape.TextFrame.AutoSize = True
        Sheets("Sheet1").Select
        .Comment.Shape.Select True
        Selection.ShapeRange.Top = 100
        Selection.ShapeRange.Left = .Left
    End If
End With
Next rngZelle
Next k

End Sub

Situation:

This code marks given events in "K" in a timeline at the right place in the year - okay!
But unfortunately it does not only mark the given date (inrow "k") in the timeline. It marks the whole timeline...


Would you be so kind to adopt the code that only the date which is given in row "K" is marked?
It is not necessary to mark a period. It should just show what is in row "K".


Thank you in advance from Germany

[snb]

I think that by using the debugging tool in the VBEditor you will be perfectly able to see what the code does and to improve it. Especially when applied to the workbook with real data (that we haven't been provided with).
Better than asking for a solution is acquiring the means, methods and ability to solve problems yourself.

[arthurbr]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[watersev]

hi, Julian Schubert, you compare each value in Sheets("Sheet1").Range("A4:NB4") with k and k.Offset(0, -1), what is the condition for colouring k?

[Julian Schubert]

Hello Waterserv,

find attached the file.
I think it is easier to look at it than to describe it.

The aim is that the code automatically looks at row "K" and marks the given dates (from "K") in the Timeline in "Sheet1".

And are you in the position to make the timeline grey(light) and mark events red?

Thank you

[Julian Schubert]

@snb:

what do you mean by

the debugging tool in the VBEditor

?

If it is what I think it is... I tried but I did not manage to solve my problems in the code...


Thank you

[watersev]

@ Julian Schubert, what should happen if August 4, 2011 is selected? We need to colour 04.08.2011 and till the NB4 on Sheet("Sheet1")?

[Julian Schubert]

Thank you waterserv.
Unfortunately an error occurs:

Run-time error "13"
Type missmatch

and this line gets marked yellow

.Range(.[a4].Offset(, DateDiff("d", "01.01.2011", Target)), .[nb4]).Interior.ThemeColor = xlThemeColorAccent1

[snb]

Debugging: use F8

DateDiff("d", Cdate("01.01.2011"), Target)

[romperstomper]

Try replacing:

DateDiff("d", "01.01.2011", Target))

with:

DateDiff("d", dateserial(2011, 1, 1), Target))

[Julian Schubert]

still not working

put in this code now

With Sheets("Sheet1")

    .Range(.[a4].Offset, (DateDiff("d", CDate("01.01.2011"), Target)), .[nb4]).Interior.ColorIndex = 3

End With

does not work....

same error occuring... missmtach... run-time error...

have you tried out the code in my file?

[watersev]

you misplaced coma and round bracket after offset, it should be:

.Range(.[a4].Offset(, DateDiff ...

[Julian Schubert]

okay now it is working - but the whole timeline is red know...

I do not see any changes :-)

It would be wonderful if just the exactly event in the timeline is red... and the line grey...

[watersev]

colour the timeline in grey, and try again

[Julian Schubert]

I already did, but it gets red every time again... do not worry I am working with you





I just saw that there is a code in "Sheet1", is that influencing the whole process?

Code "Sheet1"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("A4:NB4").Interior.ThemeColor = xlThemeColorAccent1
With ActiveSheet
    If .Comments.Count > 0 Then
        .Cells.SpecialCells(xlCellTypeComments).ClearComments
    End If
End With
End Sub

[Julian Schubert]

Thank you in advance.

[watersev]

I just saw that there is a code in "Sheet1", is that influencing the whole process?

Yes, it colours the whole row

Please check attachment, I'm using Excel 2003 so I do not have so many columns as you do, but you'll be able to see how it works. Change date 10.03.2011 to say 10.02.2011

[Julian Schubert]

sorry for the late answer - other projects...

so now my situatuion is that I am not smarter than before because I do not understand your attachement or at least how I can benefit of it

So now my timeline works, but it still marks the whole row and I would be happy if only the date in Row 'K' (for ex 22/08/2011) gets marked on the timeline.

And it only shows the first entry in row "K" in the timeline and not the following.


Furthermore, the timeline is still completely red and I would be happy if the timeline is grey and the selected dates are marked red.


In addition, is it possible that I have 3 timelines (4 years (2010/2011/2012/2013)) each under the other... ?
... I want to solve the problem that I have dates of different years in row K...


Find attached again the file.
Can you change it the way I want to have it?.

Thank you very much in advance.
Julian

[watersev]

please check attachment, insert dates in K3:K5 on Sheet2 and check result on Sheet1, the code is in Sheet2 module. It's done only for 2011

[Julian Schubert]

Hello waterserv,

Thank you for your file!

Unfortunately, it does not work - If I insert dates in K3:K5 on Sheet1 an error occurs:

Run-Time error '13'
Type missmatch

Can you help me again pls?

Thank you.

[watersev]

hi, Julian, the dates are entered on Sheet2, everything works as it should, check attachment for result of three dates entered

[Julian Schubert]

Hello waterserv,

Thank you again.

I do not why but it does not work for me... Even when I just try to change a date you have already entered.

He gives me the same 'runtime error' and marks

this row

With .[a4].Offset(, DateDiff("d", CDate("01.01.2011"), cl.Value))

yellow...


Dou you know the solution?
Do you use Excel 2007?

Thank you for your support.