User Profile
GKE2019
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Planning days colour
Hello, I have here a planning, and I would like that it automatically puts colours in. For Example: Startdate 11/02/2022 Duedate 18/02/2022 And it would colour all the cells between it. At the moment I have this RowDescription = 3 rowFirstData = 4 ColTaskName = "A" ColBucketName = "B" ColAssignedTo = "C" ColStartDate = "D" ColDueDate = "E" ColLate = "F" ColStartdayNums = "G" nColFirstData = 1 nColLastData = 6 Style01 = "01" Style02 = "02" Style03 = "03" Style04 = "04" Style05 = "05" Style06 = "06" Style07 = "07" Style08 = "08" For i = rowFirstData To qRows If Range(oColNameE & i).Value = vbNullString Then ElseIf Range(oColNameD & i).Value = vbNullString Then Else: startDay = Range(ColStartDate & i).Value endDay = Range(ColDueDate & i).Value deltaDays = endDay - startDay startdayCol = Range(ColStartdayNums & i).Column startdayCol = Cells(i, (startdayCol + startDay - 1)).Column enddayCol = Cells(i, startdayCol + deltaDays).Column If Range(ColLate & i).Value = "true" Then StyleStr = "Bad" ElseIf Range(ColBucketName & i).Value = "Mee Bezig" Then StyleStr = "02" ElseIf Range(ColBucketName & i).Value = "Planning" Then StyleStr = "01" ElseIf Range(ColBucketName & i).Value = "On Hold" Then StyleStr = "04" ElseIf Range(ColBucketName & i).Value = "Uitbesteed" Then StyleStr = "05" ElseIf Range(ColBucketName & i).Value = "Controlle" Then StyleStr = "03" ElseIf Range(ColBucketName & i).Value = "Klaar" Then StyleStr = "08" ElseIf Range(ColBucketName & i).Value = "Vrij" Then StyleStr = "06" ElseIf Range(ColBucketName & i).Value = "Op locatie" Then StyleStr = "07" End If If StyleStr <> "" Then Range(Cells(i, startWeekCol), Cells(i, endWeekCol)).Style = StyleStr End If End If Next i But so far I get an type mismatch at line 29. What am I doing wrong?Hide collums that are before the current date
Hello, I wanted to hide the collumns(G2:NG2) that came before the date of today(D2). So for example, today 11/04/2022. All the dates before that should be hidden But so far I got this: Dim rng As Range Set rng = ActiveSheet.Range("G2:NG2") For Each cell In rng If Month(cell) = Month(Date) Then Columns(cell.Column).Hidden = True End If Next cell But it doesn't work the way I want.Change weekend collumn background colour
Hello, I would like to change the background colour if the date says it is weekend. I now have this: It works but for a bit, I would like that only the weekends are highlighted. And that it would stop when there is no more string/text found at A. Right now I use this code Dim r As Range, c As Range Set r = Range(Range("G2"), Range("NG2").End(xlDown)) For Each c In r If Weekday(c) = 6 Or Weekday(c) = 7 Then c.Interior.ColorIndex = 3 End If Next c What can I do to make it work?Date formating issues teams export to excel
Hello, When I export my plan from Microsoft teams, the format of the date is still general. But when I run my the formatting of the date jumps on: This is a part of the code: Range("F5:F100").NumberFormat = "dd-mm-jjjj" Sheets("Tasks").UsedRange.Replace What:="\j\j\j\j", Replacement:="jjjj" And that doesn't work, is there a way how I can fix this?
