Changing font colour in a cell with a formula

Question

I have a spreadsheet that is used by a group of people. Its purpose is for service admin to plan runs for service techs weekly.

They input each job and a travel & task estimated time in minutes which is then converted into decimals =SUM(D11:K11)/1440 then formatted into time.

The total travel time & task time are added together =(SUM(B12,C13,B11)) and displayed as (00:00:00).

I then use conditional formatting to make it Green if under 7 hours or Red if it's over 7 hours Cell Value < 0.29167.

These steps are then repeated in rows underneath them after the tech has completed the job for and the "Actual" times are added.

The estimated total hours are subtracted from the actual total hours to get the difference =E16-E13, if it's negative they're ahead of schedule so it conditional formats to Green if it's positive they're behind so it formats to Red.

Ultimately my issue is that when a new run is added, it means all the conditional formatting messes up and needs to be reapplied. Is it possible to wrap the formulas in a IF statement to change their colors bypassing conditional formatting all together? if not what would be the best solution to the issue?

Edit

In this image you can see that Jevan was estimated to finish at 5 minutes under his 7 hours since he got stuck in traffic he ended up doing 31 minutes of overtime and was 35 minutes behind schedule.

Answer 1

You may use this VBA Macro as standard module.

Sub TestFormulas()

   For Each cell In Range("C1:C15")
     If cell.HasFormula = True Then

      If cell.Value = 3 Then
       cell.Font.Color = vbGreen
      ElseIf cell.Value >= 0.33 Then
       cell.Font.Color = vbRed
      End If
     End If

   Next
End Sub

N.B.

• Save the Workbook as Macro Enabled.
• You may adjust test value and cell references as needed.

Answer 2

Two things:

• There is no way to address conditional formats using a worksheet formula.
• Conditional formatting has a few known issues with properly applying formats when rows are inserted.

You may need to resort to VBA to either apply a fixed format or re-apply the CF rules after new data has been added.

You can use regular number formats to display positive and negative numbers in different colors, but that is just the font color, not the cell fill.

You may also want to consider separating the data entry from the reporting (coloring) aspect. If your report is on a separate sheet, using data from a data entry sheet, you probably won't have these issues.