Coloured Cells

[Ca1icoJack]

I have a loop which looks down thousands of lines and across 20 cells on each row. The code takes a huge amount of time to run, since it is looped on a lot of files. Is there a way to make the code only look at a line if the line contains colour since many are blank?

Thank you

 

[SkipVought]

Hi,

Wouldn't each cell in the line need to be interogated to determine as such?

Perhaps you ought to post your code containing the loops, in order to get some advice.

So you have ScreenUpdating turned off?

Are you referencing cells rather than SELECTING cells?

Are you using redundant referencing, that can often be mitigated using the With...End With construct?

Some of these questions can be answered by observing your code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ca1icoJack]

I've missed out all of the detal in the code except for the way the loop works, any idea on how to make this faster? (I never know where the coloured cells will begin or end)

Thanks for your help
____________________________________________________________
For R = 1 To LastRow(0)

For C = 1 To 15

If Selection.Interior.ColorIndex = 35 Then Type = ActiveCell.Value 'There are a lot of these.

ActiveCell.Offset(0, 1).Select

If Type <> "" And C = 15 Then

Sheets("Output").Select
Range("A2").Select

For T = 1 To Range("IV1").End(xlToLeft).Column

If ActiveCell.Interior.ColorIndex = 35 Then ActiveCell.Value = Type

ActiveCell.Offset(0, 1).Select

Next T

End If

Next C

ActiveCell.Offset(1, -15).Select

Next R
____________________________________________________________

 

[SkipVought]

You are selecting Sheet Output, but you never select any other sheet. WHY?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What does it mean, there are alot of these? A lot of colors you are testing for?

What color indexes? What action is taken?

BTW, I see MUCH that could shave significant time from your process.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

This is only an example, since the code that you posted is woefully undescriptive of your process. Do NOT use Type...

faq707-4105

Dim Typ     'Type is a reserve word
For r = 1 To LastRow(0)
                        
    For c = 1 To 15

        With Cells(r, c)
            Select Case .Interior.ColorIndex
                Case 32
                    
                Case 33
                
                Case 34
                    Typ = .Value
                Case Else
                
            End Select
                
            If Typ <> "" And c = 15 Then
                   
                For t = 1 To Sheets("Output").Range("IV1").End(xlToLeft).Column
                    With .Offset(0, t)
                        Select Case .Interior.ColorIndex
                            Case 32
                                
                            Case 33
                            
                            Case 34
                                Typ = .Value
                            Case Else
                            
                        End Select
                    End With
                Next t
        
            End If
        End With
    Next c
Next r

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ca1icoJack]

There are ~30 different colours the code goes through to test for.

I don't actually use Type in the code... I cut down all of the detail just to show the loop as the rest is just a repetition of the two if statements and a lot of calculations which cannot be changed. If you want to see the entire piece of code I can post that. And yes, I do reselect the original sheet, I just forgot to type that bit of code in.

Will referencing cells, as SkipVought described, make the code run a lot more quickly than moving and selecting cells then?

Thanks for the help.

 

[SkipVought]

Yes. And ScreenUpdating.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ca1icoJack]

Thanks for the help. ScreenUpdating has always been off.

I'm trying it out with your code now.