Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
With the range ("A1:Z100") in the activesheet, I would like to create
alternating patterns in the cells using ColorIndexes 2 and 15. So A1=2 B1=15 C1=2 And A2=15 B2=2 C2=15 etc How best to do this, please? Regards. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
Something as simple as that might be best done through manually recording the
macro rather than actually coding anything yourself. "Stuart" wrote: With the range ("A1:Z100") in the activesheet, I would like to create alternating patterns in the cells using ColorIndexes 2 and 15. So A1=2 B1=15 C1=2 And A2=15 B2=2 C2=15 etc How best to do this, please? Regards. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
Try this:
Sub chequer() Dim i As Long, cell As Range, r As Long, c As Long, j As Long i = 0 For r = 1 To 100 For c = 1 To 26 i = i + 1 j = (i - Int((i / 2)) * 2) Cells(r, c).Interior.ColorIndex = j * 13 + 2 Next i = i + 1 ' Force Alternate colour at beginning of next row Next End Sub HTH "Stuart" wrote: With the range ("A1:Z100") in the activesheet, I would like to create alternating patterns in the cells using ColorIndexes 2 and 15. So A1=2 B1=15 C1=2 And A2=15 B2=2 C2=15 etc How best to do this, please? Regards. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
Stuart,
Here is one approach that doesn't use programming, but instead uses Conditional Formatting. Select the range A1:Z100. Then click Format | Conditional Formatting... from the menubar. ---For condition #1: Formula Is =MOD(ROW()+COLUMN(),2) ---For condition #2: Formula Is =NOT(MOD(ROW()+COLUMN(),2)) Define the colors you want for each condition with the Format button. One benefit of this approach is that it is dynamic and inserting rows or columns will not interrupt the checkerboard pattern. I assume this is a plus. Troy "Stuart" wrote in message ... With the range ("A1:Z100") in the activesheet, I would like to create alternating patterns in the cells using ColorIndexes 2 and 15. So A1=2 B1=15 C1=2 And A2=15 B2=2 C2=15 etc How best to do this, please? Regards. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
Yes it's simple. So simple that simple me cannot see it.
Whilst setting the pattern is simple, finding an efficient way to determine which cell requires which pattern .... that is more difficult for me. Do I really have to cycle across "A1:Z1" alternating the pattern and then repeat for each row ( or via sequential columns)? I was hoping there might be some more efficient way. Regards. "Mark" wrote in message ... Something as simple as that might be best done through manually recording the macro rather than actually coding anything yourself. "Stuart" wrote: With the range ("A1:Z100") in the activesheet, I would like to create alternating patterns in the cells using ColorIndexes 2 and 15. So A1=2 B1=15 C1=2 And A2=15 B2=2 C2=15 etc How best to do this, please? Regards. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
Sub test2()
Dim r as Long, c as Long Dim rng As Range Set rng = Range("a1:z100") rng.Interior.ColorIndex = 2 'white - same as xlautomatic For c = 1 To rng.Columns.Count For r = 1 To (rng.Rows.Count - 1) Step 2 Cells(r + (c Mod 2), c).Interior.ColorIndex = 15 Next Next End Sub Regards, Peter T "Stuart" wrote in message ... With the range ("A1:Z100") in the activesheet, I would like to create alternating patterns in the cells using ColorIndexes 2 and 15. So A1=2 B1=15 C1=2 And A2=15 B2=2 C2=15 etc How best to do this, please? Regards. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
For example, I've considered this:
Assume the columns to be headed numerically 1 2 3 4 5 etc 1 W G W G W 2 G W G W G etc where W represents White and G grey. If you add the 'coordinates' of each cell it seems that if the total is Even then the cell is White, else Grey. Col3:Row2 = 3,2 summed as 5, = Odd. = grey, etc So could I use this in some way? An array? Regards. "Stuart" wrote in message ... Yes it's simple. So simple that simple me cannot see it. Whilst setting the pattern is simple, finding an efficient way to determine which cell requires which pattern .... that is more difficult for me. Do I really have to cycle across "A1:Z1" alternating the pattern and then repeat for each row ( or via sequential columns)? I was hoping there might be some more efficient way. Regards. "Mark" wrote in message ... Something as simple as that might be best done through manually recording the macro rather than actually coding anything yourself. "Stuart" wrote: With the range ("A1:Z100") in the activesheet, I would like to create alternating patterns in the cells using ColorIndexes 2 and 15. So A1=2 B1=15 C1=2 And A2=15 B2=2 C2=15 etc How best to do this, please? Regards. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
Stuart,
Have you considered the Conditional Formatting solution I provided? It adds the Row and Column index of each cell and takes the MOD2 remainder of the sum to create a 0 or 1 result for each cell. The 0 or 1 result is then interpretted as a False or True for the decision of how to format the cell. Troy "Stuart" wrote in message ... For example, I've considered this: Assume the columns to be headed numerically 1 2 3 4 5 etc 1 W G W G W 2 G W G W G etc where W represents White and G grey. If you add the 'coordinates' of each cell it seems that if the total is Even then the cell is White, else Grey. Col3:Row2 = 3,2 summed as 5, = Odd. = grey, etc So could I use this in some way? An array? Regards. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chequer-box Pattern
Yes, I see that, thanks.
Many thanks all. Regards. "TroyW" wrote in message ... Stuart, Have you considered the Conditional Formatting solution I provided? It adds the Row and Column index of each cell and takes the MOD2 remainder of the sum to create a 0 or 1 result for each cell. The 0 or 1 result is then interpretted as a False or True for the decision of how to format the cell. Troy "Stuart" wrote in message ... For example, I've considered this: Assume the columns to be headed numerically 1 2 3 4 5 etc 1 W G W G W 2 G W G W G etc where W represents White and G grey. If you add the 'coordinates' of each cell it seems that if the total is Even then the cell is White, else Grey. Col3:Row2 = 3,2 summed as 5, = Odd. = grey, etc So could I use this in some way? An array? Regards. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colour pattern | Excel Discussion (Misc queries) | |||
calculating a pattern | Excel Discussion (Misc queries) | |||
Get rid of pattern | Excel Discussion (Misc queries) | |||
Returning to 'no pattern' | Excel Discussion (Misc queries) | |||
Pattern Formula? | Excel Discussion (Misc queries) |