Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cell properties (validation/colouring) quickly
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding sums in rows and columns and colouring cells with condition | Excel Discussion (Misc queries) | |||
Adding to a range of cells for validation from a cell | Excel Programming | |||
Adding small units of time and entering them quickly | Excel Discussion (Misc queries) | |||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell? | Excel Programming | |||
adding a list validation to a cell | Excel Programming |