Help with background fill and intersecting rows and columns

[jkwest]

Hello everyone,
First of all, thank you for reading this to see if you can help me. My question is I would like to know how to create a spread sheet that has alternating rows and columns filled but the background fill stops at the intersection. I have included an image of what I mean.

Thank you very much,
John

[IMG=http://www.image-share.com/upload/3438/202.jpg]

Image-Share - image-jpg-3438-202

 

[Eric W]

Welcome to the forum.

I came close to replicating your form, but not entirely.

Starting with a new sheet, I merged cell A1:D1, A2:D2, A3:D3, A4:D4, then increased the row height of row 1.

Next, I selected the entire sheet (click the little triangle in the upper left corner), clicked Conditional Formatting > New Rule > Use a formula, and entered:

<row(),row()<45)
=AND(ROW()>5,MOD(ROW(),2)=0,COLUMN()< ROW(),ROW()< 45)
Click Format... > Fill tab > and chose a color.

Then I repeated the process with this formula:

<column()+2,column()<45,row()>=AND(COLUMN()>4,MOD(COLUMN(),2)=1,ROW()< COLUMN()+2,COLUMN()< 45,ROW()>1)

You should be able to see the numbers in the formulas which refer to specific rows or columns (4,5,45). (You could also just select the range of your worksheet that actually contains your form, and eliminate the upper row/column conditions from the formulas.) That handles all the highlighting except for the top row, which is where things get dicey. If you change the second rule to:

<column()+2,column()<45)
=AND(COLUMN()>4,MOD(COLUMN(),2)=1,ROW()< COLUMN()+2,COLUMN()< 45)

then the coloring goes to the top row, but it does not go at an angle. You can get the text to go at an angle, by selecting the top row, right click on it > select Format Cells > Alignment tab > and change the Orientation to 45 degrees. I came up with 3 ideas to try to get the shading to match the text angle, perhaps one will work for you.

First, use the second version of the second formula and change the orientation of the text to 90 degrees.

Second, use the first version of the second formula, change the orientation to 45 degrees, and alternate the font color to match the background.

Third, use the first version of the second formula, insert a Shape from the Insert tab, preferably a parallelogram, change the shape to match your sheet, change the background color to match the highlighting, change the Transparency to 50%, and then change the orientation of the text in the cell under it to match. Then you can copy that to each of the other columns that need it. Probably the most work, but also the closest to your sample.

Hope this helps!</column()+2,column()<45)
</column()+2,column()<45,row()></row(),row()<45)

 

[B___P]

Hi jkwest, nice post!

Here my vba solution:

Option Explicit
Sub squared_fill()
Dim r1 As Long: Dim r2 As Long
Dim c1 As Long: Dim c2 As Long
Dim counter As Long: Dim rc As Long

r1 = Selection.Row
c1 = Selection.Column
r2 = r1 + Selection.Rows.Count - 1
c2 = c1 + Selection.Columns.Count - 1
counter = 0
For rc = IIf(Selection.Rows.Count <= Selection.Columns.Count, r1, c1) To IIf(Selection.Rows.Count <= Selection.Columns.Count, r2, c2)
    If (counter Mod 2) = 0 Then
        Range(Cells(r1 + counter, 1), Cells(r1 + counter, c1 + counter)).Interior.Color = 49407
        Range(Cells(1, c1 + counter), Cells(r1 + counter, c1 + counter)).Interior.Color = 49407
    End If
    counter = counter + 1
Next rc
End Sub

Select a square range (starting from 1st intersection) and then run macro. If selected range is not a square, macro uses smallest dimension.
It is a first try...

Hope this helps

 

[jkwest]

Thanks alot guys, you're awesome. I'll try these solutions out first thing Monday morning.