VBA IF Statement & Conditonal Formatting

Hi, I wonder whether someone may be able to help me please:

I'm using the code below to apply formulas and formatting to sheet ranges in a sheet array.

For Each ws In Worksheets(Array("Projects", "Direct Activities", "Enhancements", "Indirect Activities", "Overheads"))
        
        LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
        If LastRow >= StartRow Then
            If ws.Name = "Projects" Then
                ws.Range("B5:G" & LastRow).Cells.Font.Name = "Lucinda Sans"
                ws.Range("B5:G" & LastRow).Cells.Font.Size = 10
                ws.Range("E5:G" & LastRow).NumberFormat = "#,##0.00"
                ws.Range("E5:G" & LastRow).HorizontalAlignment = xlCenter
                If ws.Range("E5:E" & LastRow).Value > 0 Then
                    ws.Range("G5:G" & LastRow).FormulaR1C1 = "=RC5-RC6"
                Else
                    ws.Range("G5:G" & LastRow).Value = ""
                End If
           End If
       End If
Next ws
End Sub

The problem I have is two fold:

The first, is in relation to this piece of code:

 
If ws.Range("E5:E" & LastRow).Value > 0 Then
   ws.Range("G5:G" & LastRow).FormulaR1C1 = "=RC5-RC6"
Else
   ws.Range("G5:G" & LastRow).Value = ""
End If

I'm trying to apply the formula 'RC5-RC6' to column G, but only if the value in column E on the same row is greater than zero. If the value in column E equals zero, I'd like the cell in column G to be blank.

But unfortunately, when I run this I recieve a Run time '13' error, with debug highlighting this line as the cause:

If ws.Range("E5:E" & LastRow).Value > 0 Then

The second issue I have is in relation to the result of the above formula shown in column G.

I'm trying to apply conditional formatting, whereby:

• If the value is less than zero the font color is red,

• If thwe value is zero, or greater than zero, change the font color to green.

I've been working in both of these issue for a few hours now, but I've been unable to find a solution.

I just wondered whether someone could possibly look at this please and offer soem guidance on how I may go about achieving this.

Many thanks and kind regards

Hi @OllieB, thank you once again for taking the time to reply to my post and for the guidance. You clearly have a greater knowledge than I.

In respect of the formula, I think I'm right in saying that option 2 would be be more efficient? If so, could you perhaps elaborate on how I may incorporate this please?

In respect of the conditional formatting, I understand what you're saying, but I'm trying to take away as much manual intervention as possible, because the macro I'm building may be used by colleagues, who have very little knowledge of Excel.

Many thanks and kind regards

Hi @OllieB thank you very much for this on both counts.

I've amended the code you suggested, but unfortunately I receive the following error message: Run time error '1004': Application-defined or object-defined.

Debug highlights the line I have just introduced as the cause?

Many thanks and kind regards

Hi @OllieB, thank you very much for coming back to me with this.

I has to tweak the 'RC' references a little, but it works great.

All the best and kind regards