Scott
12/12/2006 4:25:00 PM
Thanks for your response, but I oversimplified. It happens I guess.
:-)
The Mod formula I put is a derived result from the value in the cell.
The cell itself generally contains 0-9/A-Z which has to get validated
against the rest of the cells in that row to determine if it needs to
be changed. The result is I end up with the original set of values and
a derived set of values (which essentially could be reduced to a
TRUE/FALSE list) for the row, and it's this derived set which I'm using
to determine colouring.
I did have formulas to do this, but it reached the point where they
were too time consuming -- 1/4+ seconds per cell... not bad if you're
not doing it too often, but dealing with changing fifty thousand or
more cells over a session, it was considerable -- and there were a
couple other issues, like file size that came into play.
The cell by cell stuff i'm currently doing is considerably faster than
that, but I was hoping there was a way to do it faster yet. Comparing
the performance of obtaining the data, doing the manipulations to
determine the status, and outputting the results -- the output is by
far the most time consuming.
I had also considered (although it isn't really what I want to do, and
probably doesn't help for putting the validation in the same cells)
adding a helper column for each cell. Then, I could just dump 1/0 in
the cell, and use that for conditional formatting. But, that doesn't
fix the validation side of things, I don't think. (That said, I
haven't compared the relative speed of updating colour to that of
updating validation.)
Sorry, I am rather long winded.
Scott
Bob Phillips wrote:
> I would use CF for that first colouring like so
>
> Dim WS As Worksheet
>
> Set WS = Worksheets("Sheet1")
>
> With WS.Range("A1:A100")
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=MOD(INDIRECT(""Data!A""&ROW()),2)<>0"
> .FormatConditions(1).Interior.ColorIndex = 3
> End With
>
> which you can extend for the rest.
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "Scott" <maistrye@gmail.com> wrote in message
> news:1165907886.105311.312560@16g2000cwy.googlegroups.com...
> > Hi,
> >
> > I was wondering if there is a way to add Validation and Colouring to a
> > set of cells in a way that is faster than doing it cell by cell for a
> > whole region.
> >
> > I have an arbitrary region of cells, each of which need their colour
> > and validation toggled based on the status of their adjoining cells.
> > Currently, I'm going through cell by cell and changing the colour and
> > validation. It works, but is sort of slow.
> >
> > I was wondering if there is a way similar to grabbing a whole region of
> > Values and pasting a whole region of Values for properties of cells.
> >
> > I'd prefer an example, if at all possible. Here's a comparable bit of
> > code, but does the colouring cell by cell instead of determining the
> > colouring for all cells, then Pasting it somehow to them all.
> >
> > Dim WS As Worksheet
> > Dim Data As Variant
> > Dim i As Long
> >
> > Set WS = Worksheets("Sheet1")
> > Data = WS.Range("A1:A100")
> >
> > For i = 1 To 100
> > With WS.Cells(i, 1).Interior
> > If Data(i, 1) Mod 2 = 0 Then
> > .ColorIndex = xlNone
> > Else
> > .ColorIndex = 3
> > .Pattern = xlSolid
> > End If
> > End With
> > Next i
> >
> > Please note that I would like to preserve the remainder of the existing
> > data... I only want to change the colour.
> >
> > I'm assuming Validation is similar. If for your suggestion it isn't,
> > please clarify if possible.
> >
> > Thanks,
> > Scott
> >