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