Excel Conditional Formatting limits (Office 2k)

Topic

New Reply

I do a lot of work in pivot tables, in which conditional formatting seems to work as the dimensions of the data area change in response to the criteria input. What you cannot do, of course, is predict what happens to the dimensions of that table.

I have despired at the limit of three conditions (four or even five by clever range and default setting). I’m sure someone could help me with VBA code and a macro….

I’d like to be able to set up a little matrix of values (typically numberic) and formats. I’d do this by hand and it would be unique to each worksheet. I would then like to highlight a range with values in it (from the pivot), and then click on a button to apply the “lookup” formatting to the highlighted range.

Does this seem possible?

Thanks,

Mike C

Reply | Quote

Replies

Let me see if I can restate your request:
You have a pivot table.
You want to look thru the pivot table and look at the values (possibly with certain, row and column headings)
Then “Lookup” the value in a table of “formats” (the table could have just numbers or also other conditions)
Based on the value looked up, it would format the cell in the pivot table, with the format of the cell in the lookup table.

Is that what you are after? This is “doable” in VB, but might be “sluggish” esp with larger tables.

Steve

Reply | Quote

Steve, thanks for being interested
using your post as my base case…….

“Let me see if I can restate your request:

You have a pivot table. – YES, SEVERAL, ALL ANALYSED AND RE-ANALYSED, AND THEREFORE CHANGING DIMENSIONS EACH TIME.

You want to look thru the pivot table and look at the values (possibly with certain, row and column headings). YES, AND I WOULD BE HAPPY TO SELECT THE RANGE OF CELLS THAT I WANTED TO FORMAT. PROBABLY WITHOUT THE ROW AND COLUMN HEADINGS BITS UNLESS YOU WERE GOING TO OFFER TO HAVE THE CONDITIONING APPLIED / NOT APPLIED DEPENDANT ON THEM!

Then “Lookup” the value OF EACH CELL in a table of “formats” (the table could have just numbers or also other conditions). YES. I’D SORT OF ASSUMED NUMBERS, AND THAT THERE WOULD BE SOME SENSE OF CONTINUITY I.E. THAT THE DEFINITIONS WERE FOR A SET OF CONTIGIOUS BANDS OF INCREASING VALUE

Based on the value looked up, it would format the cell in the pivot table, with the format of the cell in the lookup table. YES

Is that what you are after? This is “doable” in VB, but might be “sluggish” esp with larger tables. OK, BUT, ALMOST BY DEFINITION PIVOT TABLES TEND TO BE MUCH SMALLER THAN THEIR SOURCE DATA.

So, you basically had it right Steve, but by making me clarify it I guess assumptions about contigious values come out into the open!

cheers,

Mike C

Steve

Reply | Quote

Well, since I seem to understand what you want, and I think it is “doable”, could you provide:

1) a few “sample” “pivot tables (you say the size will change so having a few “potential tables” will help get an idea of what to “look for”. These do not have to be real data, just some ideas of what to look for and to work with. These should be “set up” as a BEFORE (how you would get them after a refresh)

2) a copy of the above tables FORMATTED as desired (the “AFTER”), [you should “manually” set the formatting]

3)The “formatting requirements” basis what ranges define what formatting

With this info, table of formatting options, could be created, and then a macro created to read the “before” pivot table and have it formatted to look like the “After”.
RIght now it doesn’t have to be ALL the formatting options, just a few representative ones to get an idea of the principle. The “formatting options” do not have to be in any particular format at this time, I have some ideas of a setup, but nothing worked out. It’s setup, will be made as practical to coding.

Steve

Reply | Quote

Steve, I am very grateful, but this thing seems to be growing each time we exchange!

I attach a spreadsheet (zipped – ok?) which has a number of sheets in it which show some larger and some smaller pivot tables.

On tab “big” is quite a large one and at D2 I have a small lookup range which might contain the value bands I am interested in. If there were a logical gap in the ranges I would insert dummy ranges with a null format to make the set complete….. This one only has three conditions so that I can do it by hand

On tab “difficult” I show the most boring one I have done lately. it has two characteristics which meant I had to do it by hand. first, only every other line is formatted – the vehicle count stays plain, the average duration formatted. Second there are four colour bands plus clear

How does this do?

Mike C

Reply | Quote

Mike,
Just wanted to post and say that I haven’t forgot about your request. I have been busy at work with some customer problems as well aso some other responsibilities and I haven’t had the chance to sit down and bang out some code for you.

I am hoping to find some time this weekend or early next week.

Steve

Reply | Quote

Steve,

its absolutely fine. Thanks for staying with it

Mike C

Reply | Quote

Steve,

its absolutely fine. Thanks for staying with it

Mike C

Reply | Quote

Mike,
Well I guess it was actually “late” not “early next week”. Things were more hectic at work and I was doing some work at home at nite

I have created several routines:
The first does the “brunt of the work”. It is given 2 ranges: the range of the format and the range to be formatted. Optionally you can specify a “Step” value for skipping rows and a different starting value in case you do not want to start in the first row of the range. The format range is setup in one column like:

0.1

13

45

You would format the cell as desired. I set it up to look at the interior color, font color, fontstyle, and fontname (though you can add more easily). It does a lookup so the above means the <0.1 is not changed 0.1 to <13 is the color of the 0.1 cell, if the value is 13 to <45 is the "13" cell color and anything 45 and greater is the color of the "45" cell.

The program reads the range of format into an array, it then reads though the data and creates a combined range of all the ranges that meet the various criteria. Afterwards it formats each range to the appropriate color. It can take a while for large ranges.

The format is stored in a custom variable type. The main sub (FormatRange) calls the sub, GetArrayFormat, which puts the format range into an array, and the functions MyMatch which is a variant of excels match where instead of giving me an error with no match it reports a zero (it uses the MATCH function in excel and traps the error) and myUnion which is a variant of UNION for when one of the ranges is nothing.

You would add the line of code to your routine:

Call FormatRange(rngLookup:=Range("e72:e75"), _
    rngOutput:=ActiveSheet.PivotTables(1).DataBodyRange, _
    iStep:=2, iStart:=2)

or even:

Call FormatRange(Range("e72:e75"), _
    ActiveSheet.PivotTables(1).DataBodyRange, 2,2)

Or you could explicitly define the range to lookup instead of using the pivot table property.

Call FormatRange(Range("e72:e75"), Range("B10:AF65"), 2, 2)

Using the pivot table property will automatically adjust after the refresh. you could even call this line after the pivot table on refresh event.

If you do want to work with the whole range:

Call FormatRange(Range("e72:e75"), Range("B10:AF65"), 1, 1)

or drop the optional parameters:

Call FormatRange(Range("e72:e75"), Range("B10:AF65"))

If you name the pivots and create a name of the fomat range based on the pivot range, you could update all in one whoop by looping thru the worksheets, looping the the pivottables on each sheet checking for a format range name and if it exists call the routine to format it.

Hope this helps,
Steve

Option Explicit
Type CellFormat
    lIntColorIndex As Long
    sName As String
    sFontStyle As String
    lFontColorIndex As Long
End Type

Sub FormatRange(rngLookup As Range, rngOutput As Range, _
    Optional iStep As Integer = 1, Optional iStart As Integer = 1)
   
    Dim rArray() As Range
    Dim cfArray() As CellFormat
    Dim lIndex As Long
    Dim x As Long
    Dim y As Integer
    Dim lRowsCount As Long

    lRowsCount = rngLookup.Rows.Count
    ReDim cfArray(1 To lRowsCount)
    ReDim rArray(1 To lRowsCount)
    
    Call GetArrayFormat(rngLookup, cfArray)
    
    With rngOutput
        For x = iStart To .Rows.Count Step iStep
            For y = 1 To .Columns.Count
                If Not (IsEmpty(.Cells(x, y))) Then
                    lIndex = myMatch(.Cells(x, y).Value, rngLookup)
                    If lIndex > 0 Then
                        Set rArray(lIndex) = _
                            MyUnion(rArray(lIndex), .Cells(x, y))
                    End If
                End If
            Next y
        Next x
    End With
    For x = 1 To lRowsCount
        If Not rArray(x) Is Nothing Then
            rArray(x).Interior.ColorIndex = _
                cfArray(x).lIntColorIndex
            With rArray(x).Font
                .ColorIndex = cfArray(x).lFontColorIndex
                .Name = cfArray(x).sName
                .FontStyle = cfArray(x).sFontStyle
            End With
        End If
    Next
End Sub
    
Sub GetArrayFormat(rng As Range, cfArray() As CellFormat)
    Dim x As Long
    For x = 1 To rng.Rows.Count
        With rng.Cells(x, 1).Font
            cfArray(x).lFontColorIndex = .ColorIndex
            cfArray(x).sName = .Name
            cfArray(x).sFontStyle = .FontStyle
        End With
        cfArray(x).lIntColorIndex = _
            rng.Cells(x, 1).Interior.ColorIndex
    Next
End Sub
    
Function myMatch(vValue, rng As Range) As Long
    Dim AF As WorksheetFunction
    Set AF = Application.WorksheetFunction
    myMatch = 0
    On Error Resume Next
    myMatch = AF.Match(vValue, rng, True)
    On Error GoTo 0
End Function
    
Function MyUnion(rng1, rng2) As Range
    If rng1 Is Nothing Then
        Set rng1 = rng2
    Else
        Set rng1 = Union(rng1, rng2)
    End If
    Set MyUnion = rng1
End Function

Reply | Quote

Steve,

thank you very much for all that work. It will take me some time to get my mind around what you are saying. Be assured that any silence at my end is also due to a crowded schedule – I shall probably not sit down to get my mind round it till later in the week.

Thanks again,

Mike C

Reply | Quote

Steve,

thank you very much for all that work. It will take me some time to get my mind around what you are saying. Be assured that any silence at my end is also due to a crowded schedule – I shall probably not sit down to get my mind round it till later in the week.

Thanks again,

Mike C

Reply | Quote

Mike,
Well I guess it was actually “late” not “early next week”. Things were more hectic at work and I was doing some work at home at nite

I have created several routines:
The first does the “brunt of the work”. It is given 2 ranges: the range of the format and the range to be formatted. Optionally you can specify a “Step” value for skipping rows and a different starting value in case you do not want to start in the first row of the range. The format range is setup in one column like:

0.1

13

45

You would format the cell as desired. I set it up to look at the interior color, font color, fontstyle, and fontname (though you can add more easily). It does a lookup so the above means the <0.1 is not changed 0.1 to <13 is the color of the 0.1 cell, if the value is 13 to <45 is the "13" cell color and anything 45 and greater is the color of the "45" cell.

The program reads the range of format into an array, it then reads though the data and creates a combined range of all the ranges that meet the various criteria. Afterwards it formats each range to the appropriate color. It can take a while for large ranges.

The format is stored in a custom variable type. The main sub (FormatRange) calls the sub, GetArrayFormat, which puts the format range into an array, and the functions MyMatch which is a variant of excels match where instead of giving me an error with no match it reports a zero (it uses the MATCH function in excel and traps the error) and myUnion which is a variant of UNION for when one of the ranges is nothing.

You would add the line of code to your routine:

Call FormatRange(rngLookup:=Range("e72:e75"), _
    rngOutput:=ActiveSheet.PivotTables(1).DataBodyRange, _
    iStep:=2, iStart:=2)

or even:

Call FormatRange(Range("e72:e75"), _
    ActiveSheet.PivotTables(1).DataBodyRange, 2,2)

Or you could explicitly define the range to lookup instead of using the pivot table property.

Call FormatRange(Range("e72:e75"), Range("B10:AF65"), 2, 2)

Using the pivot table property will automatically adjust after the refresh. you could even call this line after the pivot table on refresh event.

If you do want to work with the whole range:

Call FormatRange(Range("e72:e75"), Range("B10:AF65"), 1, 1)

or drop the optional parameters:

Call FormatRange(Range("e72:e75"), Range("B10:AF65"))

If you name the pivots and create a name of the fomat range based on the pivot range, you could update all in one whoop by looping thru the worksheets, looping the the pivottables on each sheet checking for a format range name and if it exists call the routine to format it.

Hope this helps,
Steve

Option Explicit
Type CellFormat
    lIntColorIndex As Long
    sName As String
    sFontStyle As String
    lFontColorIndex As Long
End Type

Sub FormatRange(rngLookup As Range, rngOutput As Range, _
    Optional iStep As Integer = 1, Optional iStart As Integer = 1)
   
    Dim rArray() As Range
    Dim cfArray() As CellFormat
    Dim lIndex As Long
    Dim x As Long
    Dim y As Integer
    Dim lRowsCount As Long

    lRowsCount = rngLookup.Rows.Count
    ReDim cfArray(1 To lRowsCount)
    ReDim rArray(1 To lRowsCount)
    
    Call GetArrayFormat(rngLookup, cfArray)
    
    With rngOutput
        For x = iStart To .Rows.Count Step iStep
            For y = 1 To .Columns.Count
                If Not (IsEmpty(.Cells(x, y))) Then
                    lIndex = myMatch(.Cells(x, y).Value, rngLookup)
                    If lIndex > 0 Then
                        Set rArray(lIndex) = _
                            MyUnion(rArray(lIndex), .Cells(x, y))
                    End If
                End If
            Next y
        Next x
    End With
    For x = 1 To lRowsCount
        If Not rArray(x) Is Nothing Then
            rArray(x).Interior.ColorIndex = _
                cfArray(x).lIntColorIndex
            With rArray(x).Font
                .ColorIndex = cfArray(x).lFontColorIndex
                .Name = cfArray(x).sName
                .FontStyle = cfArray(x).sFontStyle
            End With
        End If
    Next
End Sub
    
Sub GetArrayFormat(rng As Range, cfArray() As CellFormat)
    Dim x As Long
    For x = 1 To rng.Rows.Count
        With rng.Cells(x, 1).Font
            cfArray(x).lFontColorIndex = .ColorIndex
            cfArray(x).sName = .Name
            cfArray(x).sFontStyle = .FontStyle
        End With
        cfArray(x).lIntColorIndex = _
            rng.Cells(x, 1).Interior.ColorIndex
    Next
End Sub
    
Function myMatch(vValue, rng As Range) As Long
    Dim AF As WorksheetFunction
    Set AF = Application.WorksheetFunction
    myMatch = 0
    On Error Resume Next
    myMatch = AF.Match(vValue, rng, True)
    On Error GoTo 0
End Function
    
Function MyUnion(rng1, rng2) As Range
    If rng1 Is Nothing Then
        Set rng1 = rng2
    Else
        Set rng1 = Union(rng1, rng2)
    End If
    Set MyUnion = rng1
End Function

Reply | Quote

Mike,
Just wanted to post and say that I haven’t forgot about your request. I have been busy at work with some customer problems as well aso some other responsibilities and I haven’t had the chance to sit down and bang out some code for you.

I am hoping to find some time this weekend or early next week.

Steve

Reply | Quote

Steve, I am very grateful, but this thing seems to be growing each time we exchange!

I attach a spreadsheet (zipped – ok?) which has a number of sheets in it which show some larger and some smaller pivot tables.

On tab “big” is quite a large one and at D2 I have a small lookup range which might contain the value bands I am interested in. If there were a logical gap in the ranges I would insert dummy ranges with a null format to make the set complete….. This one only has three conditions so that I can do it by hand

On tab “difficult” I show the most boring one I have done lately. it has two characteristics which meant I had to do it by hand. first, only every other line is formatted – the vehicle count stays plain, the average duration formatted. Second there are four colour bands plus clear

How does this do?

Mike C

Reply | Quote

Well, since I seem to understand what you want, and I think it is “doable”, could you provide:

1) a few “sample” “pivot tables (you say the size will change so having a few “potential tables” will help get an idea of what to “look for”. These do not have to be real data, just some ideas of what to look for and to work with. These should be “set up” as a BEFORE (how you would get them after a refresh)

2) a copy of the above tables FORMATTED as desired (the “AFTER”), [you should “manually” set the formatting]

3)The “formatting requirements” basis what ranges define what formatting

With this info, table of formatting options, could be created, and then a macro created to read the “before” pivot table and have it formatted to look like the “After”.
RIght now it doesn’t have to be ALL the formatting options, just a few representative ones to get an idea of the principle. The “formatting options” do not have to be in any particular format at this time, I have some ideas of a setup, but nothing worked out. It’s setup, will be made as practical to coding.

Steve

Reply | Quote

Steve, thanks for being interested
using your post as my base case…….

“Let me see if I can restate your request:

You have a pivot table. – YES, SEVERAL, ALL ANALYSED AND RE-ANALYSED, AND THEREFORE CHANGING DIMENSIONS EACH TIME.

You want to look thru the pivot table and look at the values (possibly with certain, row and column headings). YES, AND I WOULD BE HAPPY TO SELECT THE RANGE OF CELLS THAT I WANTED TO FORMAT. PROBABLY WITHOUT THE ROW AND COLUMN HEADINGS BITS UNLESS YOU WERE GOING TO OFFER TO HAVE THE CONDITIONING APPLIED / NOT APPLIED DEPENDANT ON THEM!

Then “Lookup” the value OF EACH CELL in a table of “formats” (the table could have just numbers or also other conditions). YES. I’D SORT OF ASSUMED NUMBERS, AND THAT THERE WOULD BE SOME SENSE OF CONTINUITY I.E. THAT THE DEFINITIONS WERE FOR A SET OF CONTIGIOUS BANDS OF INCREASING VALUE

Based on the value looked up, it would format the cell in the pivot table, with the format of the cell in the lookup table. YES

Is that what you are after? This is “doable” in VB, but might be “sluggish” esp with larger tables. OK, BUT, ALMOST BY DEFINITION PIVOT TABLES TEND TO BE MUCH SMALLER THAN THEIR SOURCE DATA.

So, you basically had it right Steve, but by making me clarify it I guess assumptions about contigious values come out into the open!

cheers,

Mike C

Steve

Reply | Quote

Let me see if I can restate your request:
You have a pivot table.
You want to look thru the pivot table and look at the values (possibly with certain, row and column headings)
Then “Lookup” the value in a table of “formats” (the table could have just numbers or also other conditions)
Based on the value looked up, it would format the cell in the pivot table, with the format of the cell in the lookup table.

Is that what you are after? This is “doable” in VB, but might be “sluggish” esp with larger tables.

Steve

Reply | Quote