assigning a formula to all cells in a range

[Aclare87]

Hey,

I'm trying to apply the = Round formula to all cells within a range (I'm ultimately trying to set all (-) values in a range to red with surrounding parenthesis and set to 2 decimal points. If I don't round, when I insert the (), it shows out to about 8 decimal points even though i'd already set the numberformat = "0.00".

The round formula isn't working when I use an if statement. I'd like to find out why that's not working, but further, would like to know if it's possible to apply a formula to an entire range.

What I have so far below:

'Name raw data as range
ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
ActiveSheet.Name = "RawData"

'Add new sheet and paste data
ActiveWorkbook.Sheets.Add.Name = "SectorSumTop"
ActiveWorkbook.Sheets("RawData").Range("RawSectorData").Copy Destination:=Range("A1")
cells.EntireColumn.AutoFit
'Isolate data
Range("A2,A7:A24").EntireRow.delete
Columns("F").Copy Destination:=Columns("B")
Columns("C").delete
Columns("E:J").delete

'format data - blue, 10, arial narrow, (-) red with ()
ActiveWorkbook.ActiveSheet.Names.Add Name:="SectorTop", RefersToR1C1:="=R2C2:R5C4"
With Range("SectorTop")
.numberformat = "0.00"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Color = RGB(0, 51, 102)
.Font.Name = "Arial Narrow"
.Font.Size = 10
End With

For Each cell In Range("SectorTop")
If cell.Value < 0 Then
cell.Font.Color = RGB(255, 51, 0)
cell = "=round(" & cell & ",2)"
cell.Value = "'(" & cell & ")"
End If
Next cell

And generally looking for some variation of:

Range("SectorTop").Formula = "=Round(& cell & ,2)"


Thanks!

 

[Aclare87]

I was able to find a workaround but i'm sure there must be an easier way. Anyone?

'Name raw data as range
ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
ActiveSheet.Name = "RawData"
'Add new sheet and paste data
ActiveWorkbook.Sheets.Add.Name = "SectorSumTop"
ActiveWorkbook.Sheets("RawData").Range("RawSectorData").Copy Destination:=Range("A1")
cells.EntireColumn.AutoFit
'Isolate data
Range("A2,A7:A24").EntireRow.delete
Columns("F").Copy Destination:=Columns("B")
Columns("C").delete
Columns("E:J").delete
'format data - blue, 10, arial narrow, (-) red with ()
ActiveWorkbook.ActiveSheet.Names.Add Name:="SectorTop", RefersToR1C1:="=R2C2:R5C4"
With Range("SectorTop")
.numberformat = "0.00"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Color = RGB(0, 51, 102)
.Font.Name = "Arial Narrow"
.Font.Size = 10
End With
For Each cell In Range("SectorTop")
If cell.Value < 0 Then
cell.Select
activecell.Formula = "=ROUND(" & activecell & " ,2)"
activecell.Copy
activecell.PasteSpecial Paste:=xlPasteValues
cell.Font.Color = RGB(255, 51, 0)
cell.Value = "'(" & cell & ")"
End If
Next cell
Range("SectorTop").Copy



Thanks.