Unable to set color index property of interior class

[neemi]

Hi,

I am trying to set the conditional formatting of a particular column on a spreadsheet. I loop though the rows and set the criteria and all works well. EXCEPT when I get to about 10,000 records (not exact figure) i get the error Unable to set color index property of interior class.

Can someone please help with what the problem could be.

The code I use is as below.

 Set rCP = sht.Cells(8, 18)
            Application.StatusBar = "Applying Conditional Formatting. Please wait...."
            
            Do Until rCP.Row > (7 + rs.RecordCount)
            
                With Range("R" & rCP.Row)
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=$Q$" & rCP.Row
                    .FormatConditions(1).Interior.ColorIndex = 3
                    
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$Q$" & rCP.Row
                    .FormatConditions(2).Interior.ColorIndex = 45
            
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$Q$" & rCP.Row
                    .FormatConditions(3).Interior.ColorIndex = 43
                End With
                
            Set rCP = rCP.Offset(1, 0)
            Loop

 

[xlbo]

Why loop?

With Range("R8:R" & (7 + rs.RecordCount))
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=$Q8"
.FormatConditions(1).Interior.ColorIndex = 3

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$Q8"
.FormatConditions(2).Interior.ColorIndex = 45

.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$Q8"
.FormatConditions(3).Interior.ColorIndex = 43
End With


As long as you "absolute" the references that you don't want to move and leave the others relative, it will all work based on 1 statement - no need to loop at all

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Why are you using code? This can be done on the sheet, by selecting the entire range and then setting your three CF conditions ONE TIME. Or if you do want to use code, you do not need the loop, again, selecting the entire range and setting your CF conditions.

The key is, setting your CF relative to the cell in the TOP LEFT of the range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[neemi]

I need to do it via code because the data is repopulated acording to a user selection form and before the new data is imported the sheet (namedRange) is cleared, which clears the CF.

And Geoff, I did actually code the CF as you have stated but wierd things were happening. for example the when i was checking the Cf that was applied by going to the menu, even though the column was applied correctly (ie column Q) which was fixed, the rows did not neccesarily relate to the row it actually was. But it was very unpredictable. For example. At one time for row 8 the CF was refering to Q6138 etc. I had no idea why it was doing this. so as a work around I applied the CF row by row.

And feed back appreciated.

cheers,
Neemi

 

[SkipVought]

You must ACTUALLY SELECT the range, not just reference the range. Why? I've always wondered that myself.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[neemi]

Tried it by selecting the range!!! WOW!!! Done the trick!!!
I didn't know you had to do that! don't usually... but selecting other ranges now on formatting etc just in case!

 

[combo]

As for the code, you need a reference to range where conditional formatting is to be applied and selected one cell (active cell) that is a reference to relative addressing. So, in xlbo's code:

With Range("R8:R" & (7 + rs.RecordCount))
          [!].Cells(1,1).Select[/!]
          .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=$Q8"
          .FormatConditions(1).Interior.ColorIndex = 3

combo