Checkerboard formatting 2 Rows by 1 Column

Festus Hagen

New Member
Joined
Aug 1, 2011
Messages
40
Hi All

Does anyone know a way to do conditional formatting (VBA method would work as well) to checkerboard 2 rows by 1 column???

Thanks

-Enjoy
fh <font color="#FF0000">:</font> )_~
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Is this what you want?

Select your column

Conditional Formatting formula:

=MOD(ROW(),3)
 
Upvote 0
Well, let's say your desired range is A1:A2 which is the size of a range you specified in your post that is 2 rows by 1 column.

Select that range, go to the Conditional Formatting dialog, and use this as the formula rule:

=MOD(ROW(),2)=MOD(COLUMN(),2)

Select a pattern color and click OK.

This formula will also work for more rows than just two, or for a two-row and multiple column range.

Edit:
Or maybe/probably I am totally wrong and Hotpepper got it right.
 
Last edited:
Upvote 0
If that's what the op wants, this will work as well:

=MOD(ROW()+COLUMN(),2)
 
Upvote 0
Sorry folks, Guess I wasn't clear ...

I have a large range (couple thousand rows) that I want to checkerboard 2 rows by 1 column.

Thus cells would be colored like
GREEN WHITE GREEN WHITE
GREEN WHITE GREEN WHITE
WHITE GREEN WHITE GREEN
WHITE GREEN WHITE GREEN
GREEN WHITE GREEN WHITE
GREEN WHITE GREEN WHITE

Thanks

-Enjoy
fh <font color="#FF0000">:</font> )_~
 
Upvote 0
=(mod(row()-1,4)<2)+mod(column(),2)-1

Thanks HotPepper, but that doesn't come out as desired, it winds up Like:
GREEN GREEN GREEN
GREEN GREEN GREEN
GREEN WHITE GREEN
WHITE GREEN WHITE
GREEN GREEN GREEN
GREEN GREEN GREEN
GREEN WHITE GREEN
WHITE GREEN WHITE

-Enjoy
fh : )_~
 
Upvote 0
came out OK here (I included the formulae in the cells too):
Excel Workbook
RSTUV
4101010
4201010
43-10-10-1
44-10-10-1
4501010
4601010
47-10-10-1
48-10-10-1
4901010
5001010
51-10-10-1
52-10-10-1
5301010
5401010
55-10-10-1
56-10-10-1
Sheet6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R411. / Formula is =(MOD(ROW()-1,4)<2)+MOD(COLUMN(),2)-1Abc
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top