Conditional formatting based on multiple conditions

I am using a spreadsheet to track machine downtimes at a large manufacturing plant. The machines are identified by a four digit number and are listed in column D and their respective downtimes are in column F. I would like to set the spreadsheet up so that if the same machine is down for a certain amount of time (say 16 hrs) all the rows which contain that machines 4 digit number are highlighted yellow and if the machine is down for 20 hours the rows are highlighted red. The spreadsheet may possibly be tracking hundreds of machines so if there is a way for a macro to automatically look at entries with the same 4 digit identifying number, evaluate and possibly highlight them without me physically entering all the machine numbers into the code that would be ideal. I've attached a sample file. I have very little experience with VBA and this definately above my head. Please help.

If your intent is to have a unique color for each machine and there are hundreds of machines, then this will be a visual mess.

I suggest you create a downtime report based on criteria to see either specific machines are all machines. Such as using Advanced Filter to extract the relevant records into a report sheet.

Your current structure uses a sheet for each day of the week. IMO, you are much better served by using a single sheet to contain all of the data, using a date column which is currently lacking.

If desired, you could create a "data entry" sheet and use VBA code to feed the entries to the database sheet.

With one sheet as the database, extracting records is easy and efficient and you have much more flexibility to do other analyses.

See this page: Efficient Spreahsheet Design

Other information that varies on a daily basis will be added to the spreadsheet later so the spreadsheet must be set up this way. And I do not intend to have a unique color for every machine, just two colors yellow and red. I would like the machines which have a total weekly downtime (DTTracker tab) of between 16-25 hours to be highlighted yellow and those 25+ red. This is intended to just be a heads up so people who just glance at the DTTracker tab can see which machines have had significant downtimes.

please see attachment

I assumed information is filled from left to the right (i.e. at first you fill "Machine #" and after that "Downtime" column). Input "Machine #" and "Downtime" and check result.

Originally Posted by bjvick

Watersev, I can't figure out what you did.

It works with macro. Insert 1382 in cell D28 and put 20 in cell F28. It works as you described except only column "Machine ID" is coloured.

Private Sub worksheet_change(ByVal target As Range)
Dim x As Double, y As Long, icolor As Integer
If target.Count > 1 Or IsEmpty(target) Or Cells(target.Row, target.Column - 2) = "" Then Exit Sub
x = WorksheetFunction.SumIf([d:d], target.Offset(0, -2), [f:f])
If target.Column = 6 Then
    If x >= 16 Then
        y = target.Offset(0, -2).Value
        Select Case x
            Case 16 To 25: icolor = 6
            Case Is > 25: icolor = 3
        End Select
        Application.ReplaceFormat.Interior.ColorIndex = icolor
    Range("d7:d" & target.Row).Replace what:=y, replacement:=y, ReplaceFormat:=True
    End If
End If
End Sub