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.

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.

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.

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.

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.

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.

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.

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.

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.