Home

Alternated coloured columns - in pairs

%3CLINGO-SUB%20id%3D%22lingo-sub-480313%22%20slang%3D%22en-US%22%3EAlternated%20coloured%20columns%20-%20in%20pairs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-480313%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20got%20a%20spreadsheet%20where%20all%20columns%20(apart%20from%20headings%20in%20col%20A)%20are%20in%20pairs.%20I%20would%20like%20all%20pairs%20of%20columns%20to%20be%20shaded%20in%20simply%20alternate%20colours.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20Cols%20B%26amp%3BC%20would%20be%20very%20light%20blue%2C%20D%26amp%3BE%20very%20light%20grey%2C%20F%26amp%3BG%20very%20light%20blue%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20found%20plenty%20of%20google%20hits%20explaining%20how%20to%20do%20this%20for%20single%20columns%2C%20but%20I've%20not%20been%20able%20to%20find%20a%20solution%20for%20pairs%20of%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIncidentally%2C%20I'd%20still%20like%20all%20cell%20borders%20to%20be%20visible%20(default%20colour%20being%20fine).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-480313%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-480569%22%20slang%3D%22en-US%22%3ERe%3A%20Alternated%20coloured%20columns%20-%20in%20pairs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-480569%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20just%20read%20that%20this%20should%20be%20in%20Answers.com.%20Very%20confusing%20Microsoft%20for%20us%20non%20regular%20visitors.%20I'll%20post%20it%20there.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

I've got a spreadsheet where all columns (apart from headings in col A) are in pairs. I would like all pairs of columns to be shaded in simply alternate colours. 

So Cols B&C would be very light blue, D&E very light grey, F&G very light blue etc.

 

I've found plenty of google hits explaining how to do this for single columns, but I've not been able to find a solution for pairs of columns.

 

Incidentally, I'd still like all cell borders to be visible (default colour being fine).

 

Any suggestions would be welcome.

 

Thanks

3 Replies

I've just read that this should be in Answers.com. Very confusing Microsoft for us non regular visitors. I'll post it there. 

I advise you to simply select the columns starting with row 2 and apply fill the pertinent fill color. If you prefer conditional formatting, which I personally dislike for being volatile and thus slow down calculations, select the pertinent range and apply these conditional formatting rules:
For very light gray: =(AND((MOD(COLUMN(),4)=0),(MOD(COLUMN((),4,)=1))
For very light blue: =(AND(ISEVEN(COLUMN()),ISODD(COLUMN()))
Ensure that you check the box for Stop if true for rule 1.
The choice is yours.

@Deleted 

you can use conditional formatting.

Capture.JPG

the formulas for the conditional formatting showing on the picture are:

=OR(MOD(COLUMN(B2)-COLUMN($B2),4)=2,MOD(COLUMN(B2)-COLUMN($B2),4)=3)

and

=OR(MOD(COLUMN(B2)-COLUMN($B2),4)=0,MOD(COLUMN(B2)-COLUMN($B2),4)=1)

When indicating the format to apply with each rule choose a color for the interior of the cell and a color for the border.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies