Conditional Formatting based on a time cell

Question

HI,

I am trying to create a condition in the mastering service column based on the distribution time, it should use the RAG Table times , Good Service is Green, Minor Disruption Amber and Major disruption is Red but based so basically if someone puts a time 0f 8:15 or before in distribution column it should auto fill in the mastering service as a good service (Green) and so on.

Then in the commentary column If there is Minor Disruption or Major they get a warning saying please add a comment

Date	Data Point	Name	Delivery	Distribution	FOS No crunch	Prices Sign off	Status	Mastering Service	Commentary
24/11/2023	Closing	Closing	06:33:00	07:52:00	Complete	Complete	Complete	Good Service
24/11/2023	Intra Day: 9:30am	Intra Day: 9:30am
24/11/2023	Intra Day: 10:00am	Intra Day: 10:00am
24/11/2023	Intra Day: 12:00pm	Intra Day: 12:00pm

RAG	Closing Rag	Intra Day: 9:30am Rage	Intra Day: 10:00am Rag	Intra Day: 12:00pm Rag
Good Service	08:15:00	10:15:00	11:15:00	13:15:00
Minor Disruption	08:30:00	10:30:00	11:30:00	13:30:00
Major Disruption	08:31:00	10:31:00	11:31:00	13:31:00

Any help would be appreciated

Answer 1

Try this macro. It will run when the column E is updated. And when column is major or minor, it will pop up a windows to add a comments

========================================

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range 

Dim cell As Range 

Dim comment As String 

Set rng = Intersect(Target, Range("E:E")) 

If Not rng Is Nothing Then 

    For Each cell In rng 

        If cell.Offset(0, 4).Value = "Major Disruption" Or cell.Offset(0, 4).Value = "Minor Disruption" Then 

            comment = InputBox("Please add a comment:") 

            cell.Offset(0, 5).Value = comment 

        End If 

    Next cell 

End If 

End Sub

Answer 2

Yeh rather than a comment of please add a comment, a pop up of some sort so the user is more likely to add a comment

Answer 3

Do you mean you need a macro when Distribution time updated then a pop up to add comments in column J?

Answer 4

=IF(OR(I3="Minor Disruption",I3="Major Disruption"),"Please add a comment","")

For the comment field not something Im great with but is it possible to get a pop up box possibly VBA that when its minor or major you have to put some sort of comment ?

Answer 5

Formula in I3:

=IF(B3="Intra Day: 9:30am",IF(E3<TIME(10,15,0),"Good Service",IF(E3<TIME(10,30,0),"Minor Disruption","Major Disruption")),

IF(B3="Intra Day: 10:00am",IF(E3<TIME(11,15,0),"Good Service",IF(E3<TIME(11,30,0),"Minor Disruption","Major Disruption")),

IF(B3="Intra Day: 12:00pm",IF(E3<TIME(13,15,0),"Good Service",IF(E3<TIME(13,30,0),"Minor Disruption","Major Disruption")))))

Formula in J3:

=IF(OR(I3="Minor Disruption",I3="Major Disruption"),"Please add a comment","")

Conditional formatting:

=I2="Good Service"

=I2="Minor Disruption"

=I2="Major Disruption"