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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cell properties (validation/colouring) quickly
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cell properties (validation/colouring) quickly
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" 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 |
Reply |
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 |