Conditional Formatting not applying

Hi There,

I am having great difficulties with my conditional formatting. I have two columns that need to be compared - "C" is a static value while "E" is calculated by user input in another sheet.
I am using the condition =IF(E5<C5,1,0) ***E5 turns red if less than C5*** applied to "E5:E27", however C5 keeps changing to #REF! and it doesn't apply.
The sheet is protected so I tried to unprotect it in vba but it still wont apply. I also tried to write a macro to do the colour change but I have just made a mess of it.

Could someone please help. It is the very final part of a workbook due to go live by the end of the week.

This is what my messed up code looks like -

Sheets("Availability Report").Visible = True
Sheets("Availability Report").Activate
ActiveSheet.Unprotect "8440498"

lastrow = FindLastRow

Set rng1 = Range("E5:E" & lastrow)
Set rng2 = Range("C5:C" & lastrow)

With ActiveSheet
    For Each rcell In rng1.Cells
        If rcell.Value = "" Then
            rcell.Interior.ColorIndex = none
        ElseIf rng1.Value < rng2 Then
            rng1.Interior.ColorIndex = 22
       
        End If
     Next rcell
End With

Call SetProtection