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" wrote in message
ups.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