Conditional Formatting Columns only By Values in Column

Question

I have a bunch of columns with data like this:

 A  B   C   D
4   8   13  48
72  54  67  49
58  64  56  40
34  88  3   89
91  51  51  92
76  21  16  15

I want to set up a conditional formatting colour scale, to show the largest and smallest values in each column, compared only with the values in that column.

The issue is, for me to evaluate each column and set up a colour scale against on the values in that column I have to individually apply the colour scale to each column.

With 4 columns, that is quick. But my dataset is 100 columns long. And, when I drag the formatting over the column the formatting is stacked so I end up comparing values in A,B,C,D, etc. rather than those in the one column.

I've set up a similar spreadsheet here with similar data if that helps.

Suggestions?

Answer 1

You could use the format painter to copy the format of one column, then select the next column to apply it. If you double click the format painter, it can be painted over several selections in turn.

If that is still too manual, you could run some VBA that applies the format to a range of adjacent columns.

Sub applyCF()

    Range("A1:A24").Copy ' this is the first column with the conditional format
    For i = 2 To 20 ' copy from column B to column T
        Range(Cells(1, i), Cells(24, i)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Next i

End Sub

Adjust the start column (2 = B) and the end column (20 = T) as well as the numbers for the rows to suit your scenario.