Astaroth
02-22-2008, 04:06 AM
I have written a VBA piece to export my Project into Excel. This program includes adding conditional formating to one of the columns.
The problem is that when running the macro the excel book is created fine but the conditional formating isnt applied. If you select the column and click conditional formating the formating is there and if you then click "ok" it then applies the formating but if you click cancel the formulas remain but the formating isnt applied.
the code relevant to this part is:
Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Over View"
For Columns = 1 To (ColumnCount + 1)
Set xlCol = xlRow.Offset(0, Columns - 1)
xlCol = Columns - 1
Next Columns
rgt 2
xlCol = "Resource Name"
rgt 1
xlCol = "Start Date"
rgt 1
xlCol = "Finish Date"
rgt 1
xlCol = "RAG"
xlCol.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="R"
xlCol.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="A"
xlCol.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="G"
xlCol.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
xlCol.FormatConditions(2).Interior.Color = RGB(255, 126, 0)
xlCol.FormatConditions(3).Interior.Color = RGB(0, 255, 0)
rgt 1
xlCol = "Complete"
rgt is simply a sub to move the column across by the integer
Many thanks
The problem is that when running the macro the excel book is created fine but the conditional formating isnt applied. If you select the column and click conditional formating the formating is there and if you then click "ok" it then applies the formating but if you click cancel the formulas remain but the formating isnt applied.
the code relevant to this part is:
Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Over View"
For Columns = 1 To (ColumnCount + 1)
Set xlCol = xlRow.Offset(0, Columns - 1)
xlCol = Columns - 1
Next Columns
rgt 2
xlCol = "Resource Name"
rgt 1
xlCol = "Start Date"
rgt 1
xlCol = "Finish Date"
rgt 1
xlCol = "RAG"
xlCol.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="R"
xlCol.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="A"
xlCol.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="G"
xlCol.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
xlCol.FormatConditions(2).Interior.Color = RGB(255, 126, 0)
xlCol.FormatConditions(3).Interior.Color = RGB(0, 255, 0)
rgt 1
xlCol = "Complete"
rgt is simply a sub to move the column across by the integer
Many thanks