Hi All,
I am new to making macros with color conditions and I have been having trouble figuring out the logic to my code. I have a table with various rows of jobs with their start and end time on a given day and each of these jobs can pass through multiple machines which are also included in the data on the table. My 24 hour day starts at 1pm and end the next day at 12:59pm, this is were I am having issues because the code is looking at the hours like numbers and with my logic when the day changes from 11:30pm slot to the 12:00am slot it recognizes the later time value as lesser than the 11:30pm time value. Here is what I have until now, I have put note placeholders where this logic could be inserted. I would greatly appreciate the help
I am new to making macros with color conditions and I have been having trouble figuring out the logic to my code. I have a table with various rows of jobs with their start and end time on a given day and each of these jobs can pass through multiple machines which are also included in the data on the table. My 24 hour day starts at 1pm and end the next day at 12:59pm, this is were I am having issues because the code is looking at the hours like numbers and with my logic when the day changes from 11:30pm slot to the 12:00am slot it recognizes the later time value as lesser than the 11:30pm time value. Here is what I have until now, I have put note placeholders where this logic could be inserted. I would greatly appreciate the help
Code:
Sub cambiarColorCeldas()
Dim rango As Range
Dim celda As Range
Dim C1 As String
Dim i, j, k, flag1, flag2 As Integer
Dim j_first, j_last, i_first, i_last, k_first, k_last, hora, c_print_ini, c_print_fin As Long
'Initialize the variables depending on the location of the data in the Excel sheet
j_first = 4
j_last = 9
i_first = 3
i_last = 50
k_first = 22
k_last = 1000 'apply a big number in case there are multiple jobs to consider
hora = 3
c_print_sch = 3
c_print_cap = 2
c_print_ini = 5
c_print_fin = 6
'flag to know the day changed
flag1 = 0
'Loops that go through the schedule table and color those cells by machine and times in the planner
For j = j_first To j_last
For i = i_first To i_last
For k = k_first To k_last
If Cells(k, c_print_sch).Value <> "" Then
If i <> i_last Then
If Cells(j, c_print_cap).Value = Cells(k, c_print_sch).Value Then
'condition in case it changed day (11:30 PM to 12:00 AM)
If Cells(hora, i).Value < Cells(hora, i + 1).Value And flag1 = 0 Then
If ((Cells(k, c_print_ini).Value >= Cells(hora, i).Value) And (Cells(k, c_print_fin).Value < Cells(hora, i + 1).Value)) _
Or ((Cells(k, c_print_ini).Value < Cells(hora, i + 1).Value) And (Cells(k, c_print_fin).Value > Cells(hora, i).Value)) Then
' MsgBox (Cells(k, c_print_ini).Value) 'test
' MsgBox (Cells(hora, i).Value)
' MsgBox (Cells(k, c_print_fin).Value) 'test
Cells(j, i).Interior.ColorIndex = 48
End If
Else
If flag1 = 0 Then
If (Cells(k, c_print_fin).Value >= Cells(hora, i).Value) And (Cells(k, c_print_fin).Value < Cells(hora, i + 1).Value) Then
Cells(j, i).Interior.ColorIndex = 48
Else
'here missing how to take into consideration when there is no jobs in the other hours********
Cells(j, i).Interior.ColorIndex = 48
End If
Else
'here missing the code used when the change of day has occured and keep coloring the cells********
' MsgBox "FIN: " & Cells(k, c_print_fin).Value & " < INI:" & Cells(k, c_print_ini).Value
If (Cells(k, c_print_fin).Value < Cells(k, c_print_ini).Value) Then
If Cells(hora, i).Value < Cells(k, c_print_fin).Value Then
Cells(j, i).Interior.ColorIndex = 48
Else
Cells(j, i).Interior.ColorIndex = 48
GoTo salto
End If
End If
End If
flag1 = 1
End If
End If
End If
Else
'this code is only for the last instance of the column of hours
If Cells(j, c_print_cap).Value = Cells(k, c_print_sch).Value Then
If (Cells(k, c_print_ini).Value > Cells(hora, i).Value) Or (Cells(k, c_print_fin).Value > Cells(hora, i).Value) Then
Cells(j, i).Interior.ColorIndex = 48
End If
End If
End If
salto: Next k
Next i
flag1 = 0
Next j
End Sub
Last edited by a moderator:
