Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 1004 Error in VBA Conditional Formatting Code €“ Fails in 2007

I have a VBA sub which works in Excel 2003 but fails in Excel 2007.

When the worksheet is recalculated, such as a pivot table update, the sub is
called and the error is generated. The sub changes the font and interior
color based upon the value in two specified cells. As written the failure
occurs on the following line:

Selection.FormatConditions(2).Font.ColorIndex = 2

Interestingly I can comment out the following two lines and line above works
without issue.

Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 3

The sub follows:

Sub AddFormatConditions()
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Percent Funded'", _
xlDataAndLabel
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$G$5"
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$J$5"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions(2).Font.ColorIndex = 2
Selection.FormatConditions(2).Interior.ColorIndex = 41

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Percent Funded'", _
xlLabelOnly
Selection.FormatConditions.Delete
Range("F14").Select
Range("a1").Select
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
runtime error code 1004 Karen Excel Worksheet Functions 1 May 13th 10 12:21 AM
Conditional Formatting: Logical AND fails to work with INDIRECT(.. Hamish Excel Worksheet Functions 5 May 15th 08 04:54 PM
Error Code 1004 when exporting from VB app to Excel 2007 sue mac Excel Discussion (Misc queries) 0 September 11th 07 08:10 AM
Copying Conditional Formatting Fails Henry Stockbridge Excel Programming 5 December 14th 06 09:01 PM
VBA code to save gives error 1004??? Richard m Excel Programming 1 November 26th 03 04:10 AM


All times are GMT +1. The time now is 04:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"