Coloring Cells depending on Hourly Schedule Table

aroig07

New Member
Joined
Feb 26, 2019
Messages
35
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 :)

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:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,225
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top