Reports of all stripes with conditional formatting

Hi all,

Imagine you want alternating rows (or columns) in a report to have alternating colors. I don’t know, maybe you’re missing the days of tractor-feed paper.

One option is to convert your data in a table (Ctrl+T, or Insert \ Table). Other advantages come with the table like the ability to reference ranges in formulas with names instead of addresses, but the table doesn’t work in all cases (but I definitely should write something about tables soon).

An easy option, if you can’t use tables is to use conditional formatting.:

  • Select your data
  • Home \ Conditional Formatting \ New Rule
  • Use a formula to determine which cells to format
  • Use the following formula =ISODD(ROW())
  • Set your format, validate, you’re all set (you can also use ISEVEN, and swap COLUMN() for ROW() if you want vertical rather than horizontal stripes).

Image

Try it, you might like it.

Thomas

Advertisements

The Match Columns macro – Absolutely useless until you need it.

You have two ranges with headers. Some of the headers match, some don’t. In addition, they’re not in the same order.

Select the first range, hold Ctrl before selection the second range and run the below code. All the columns of the second range are now matched to the columns of the first, and all unmatched columns of the second range are now at the end. Tada!

Try it, you might like it,
Thomas

Sub MatchColumns()
‘Matches two ranges by inserting columns in both ranges so each value is on the same column

‘you first need to
‘ select the two data blocks you want to split and match

Dim rg1 As range, rg2 As range
Dim firstMatch As Boolean
Dim i As Long, j As Long, foundCol As Long

application.ScreenUpdating = False

If selection.Areas.Count 2 Then
MsgBox “Select two areas”
Exit Sub
End If

Set rg1 = selection.Areas(1)
Set rg2 = selection.Areas(2)

‘gets the number of unique values in the first rows of range 1 and 2, to be able to run the loop all the way
Dim cUnique As New Collection

On Error Resume Next

With rg1
For i = 1 To .Rows(1).Cells.Count
cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
Next
End With

With rg2
For i = 1 To .Rows(1).Cells.Count
cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
Next
End With

On Error GoTo 0

‘boolean needed to be able to resize range 2 if required
firstMatch = True

For i = 1 To cUnique.Count
If Len(rg1.Cells(1, i)) = 0 Or rg2.Cells(1, i) = rg1.Cells(1, i) Then
firstMatch = False
GoTo nxt_i:
End If

On Error Resume Next
foundCol = rg2.Rows(1).Find(What:=rg1.Cells(1, i), LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False).Column

If Err 0 Then
Err.Clear
rg2.Offset(, i – 1).Resize(, 1).Insert Shift:=xlToRight
If firstMatch Then Set rg2 = rg2.Offset(, -1).Resize(, rg2.Columns.Count + 1)
Else
rg2.Columns(foundCol – rg2.Column + 1).Cut
rg2.Columns(i + rg2.Column – 1).Insert Shift:=xlToRight
firstMatch = False
End If

nxt_i:

Next

application.ScreenUpdating = True
End Sub