Help with background fill and intersecting rows and columns

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,188
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)
 
Last edited:

B___P

Active Member
Joined
Oct 31, 2015
Messages
415
Hi jkwest, nice post!

Here my vba solution:
Code:
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
 
Last edited:

jkwest

New Member
Joined
Feb 10, 2017
Messages
2
Thanks alot guys, you're awesome. I'll try these solutions out first thing Monday morning.
 

Forum statistics

Threads
1,086,116
Messages
5,387,925
Members
402,089
Latest member
Exceliamus

Some videos you may like

This Week's Hot Topics

Top