Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel Conditional Formatting limits (Office 2k)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    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

  3. #3
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    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

  5. #5
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    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

  7. #7
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    Steve,

    its absolutely fine. Thanks for staying with it

    Mike C

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    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:
    <table border=1><td align=right valign=bottom>0.1</td><td align=right valign=bottom>13</td><td align=right valign=bottom>45</td></table>

    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:
    <pre>Call FormatRange(rngLookup:=Range("e72:e75"), _
    rngOutput:=ActiveSheet.PivotTables(1).DataBodyRang e, _
    iStep:=2, iStart:=2)</pre>


    or even:
    <pre>Call FormatRange(Range("e72:e75"), _
    ActiveSheet.PivotTables(1).DataBodyRange, 2,2)</pre>


    Or you could explicitly define the range to lookup instead of using the pivot table property.
    <pre>Call FormatRange(Range("e72:e75"), Range("B10:AF65"), 2, 2)</pre>


    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:
    <pre>Call FormatRange(Range("e72:e75"), Range("B10:AF65"), 1, 1)</pre>


    or drop the optional parameters:
    <pre>Call FormatRange(Range("e72:e75"), Range("B10:AF65"))</pre>


    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

    <pre>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</pre>


  9. #9
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel Conditional Formatting limits (Office 2k)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •