Colour Entire Row Base on the Value of Two Other Cells

[SamT]

The Functions in Module1 are pretty generic, they don't care where the cell is. I set up the code structure so that it is easy to add a priority level, if, for instance, you decide that what is red should be yellow and negative numbers should be Red.

The code in sheet1:

If you ever change the layout of your Tasks sheet, be sure that the Constants at the top of the code match the new column layout.

The Worksheet_change Sub: If you don't understand the comments, ask, I am not always clear in my instructions.

The SetColors Sub: It works, should'nt need to mess with it. Except, I added a fillip at the very bottom to set the Font in the Tasks Column to Bold, if it was Red. You can't miss it. If you don't like it, just comment out that IF...Then line.

The SetColorsManually Sub is only needed when your Tasks sheet has never had it's colors set or when the whole thing needs updating. To run it, place the mouse cursor inside the sub and press F5.

Module1 Code

Option Explicit

Enum Priorities
  Priority0
  Priority1
  Priority2
  Priority3
End Enum

Function PriorityDays(Cel As Range) As Long
    If Cel.Value = "" Then
    PriorityDays = Priority0
    Exit Function
  End If
  
Select Case Cel.Value
  Case Is <= 7: PriorityDays = Priority3
  Case Is <= 30: PriorityDays = Priority2
  Case Is <= 60: PriorityDays = Priority1
  Case Else: PriorityDays = Priority0
End Select
End Function

Function PriorityHours(Cel As Range) As Long
  If Cel.Value = "" Then
    PriorityHours = Priority0
    Exit Function
  End If
  
Select Case Cel.Value
  Case Is <= 20: PriorityHours = Priority3
  Case Is <= 50: PriorityHours = Priority2
  Case Is <= 100: PriorityHours = Priority1
  Case Else: PriorityHours = Priority0
End Select
End Function

Function ColorByPriority(priority As Long) As Long
  Select Case priority
    Case 0: ColorByPriority = vbBlack
    Case 1: ColorByPriority = vbBlue
    Case 2: ColorByPriority = vbGreen
    Case 3: ColorByPriority = vbRed
  End Select
End Function

Sheet1 Code

Option Explicit

'Edit all to suit
Const TaskCol As Long = 1
Const HoursCol As Long = 7
Const DaysCol As Long = 15

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrackChanges As Range

'TrackChanges must be set to those ranges that are manually changed.
'If the values in Days and Hours Remaining  are the result of formulas, then
'define these ranges as the locations where the new values of the
'formula precedents are entered
Set TrackChanges = Range(Range(Cells(11, TaskCol), Cells(10000, TaskCol)). _
                        Range(Cells(11, HoursCol), Cells(10000, HoursCol)), _
                        Range(Cells(11, DaysCol), Cells(10000, DaysCol)))
If Intersect(TrackChanges, Target) Is Nothing Then Exit Sub

SetColors Target
    
End Sub

Private Sub SetColors(Cel As Range)
Dim TimePriority As Long
Dim DatePriority As Long
Dim TaskPriority As Long
Dim Rw As Long

Rw = Cel.Row
TimePriority = PriorityHours(Cells(Rw, HoursCol))
Cells(Rw, HoursCol).Font.Color = ColorByPriority(TimePriority)

DatePriority = PriorityHours(Cells(Rw, DaysCol))
Cells(Rw, DaysCol).Font.Color = ColorByPriority(DatePriority)

If DatePriority > TimePriority Then
 TaskPriority = DatePriority
Else
 TaskPriority = TimePriority
End If

With Cells(Rw, TaskCol).Font
  .Color = ColorByPriority(TaskPriority)
  .Bold = False
   If TaskPriority = Priority3 Then .Bold = True
End With

End Sub

'Run once to set all tasks
Private Sub SetColorsManually()
Dim Cel As Range
For Each Cel In Range("A11:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  SetColors Cel
Next Cel
End Sub