VBA IF Statement & Conditonal Formatting

[hobbiton73]

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

[OllieB]

Comparing the value of a single cell via the Range(address).Value instruction is supported but you cannot use the same construction for a range of cells in the same manner. If you want to assign the formula for individual cells in column G, you will have two choices:

1. loop through all cells in the range (E5:E - last row), query the value in cell E and assign the formula or blank value to cell in column G
2. assign a single formula to all cells in the G column that takes into account the fact that column E might be 0 ("=IF(RC[-2]=0,'',RC5-RC6)")

as far as formatting red/green is concerned I would suggest to use plain cell formatting (Ctrl-1, Custom Format, for example: [Green]0;[Red]-0)

[hobbiton73]

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

[OllieB]

With regards to the first point

Replace

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

By

ws.Range("G5:G" & LastRow).FormulaR1C1 = "=IF(RC[-2]=0,'',RC5-RC6)"

with regards to the second part, just select the column where the values are show, press {Ctrl}{1}, Select category "custom" and copy & paste "[Green]0;[Red]-0" into the textbox under Type:. click OK button - that's it

nothing manual about it as it needs to be done only once.

[hobbiton73]

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

[OllieB]

Sorry, I though single quotes would do it.

Replace by formula line by

ws.Range("G5:G" & lastrow).FormulaR1C1 = "=IF(RC[-2]=0,"""",RC[5]-RC[6])"

[hobbiton73]

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