Adding cell properties (validation/colouring) quickly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 12 Dec 2006 07:18 GMT

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

Reply to this Message

Bob Phillips - 12 Dec 2006 09:53 GMT

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.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi,
>
[quoted text clipped - 40 lines]
> Thanks,
> Scott

Reply to this Message

Scott - 12 Dec 2006 16:24 GMT

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

> I would use CF for that first colouring like so
>
[quoted text clipped - 63 lines]
> > Thanks,
> > Scott

Reply to this Message