Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing Conditionally Formatted Colors

    Referring to the macro given as the solution to this Post, col Thread 29778, how can find out the code numbers of the colors that you selected for your conditionally formatted cells? When you go through the formatting process, it does not tell you these numbers -- you just click on a box in the palette.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summing Conditionally Formatted Colors

    Hi Stephen,

    You could record a macro while you are setting the colors, and then examine the resultant code but that seems impractical. Use someting like the following code to show the conditional font colors used (there can be up to 3) for the active cell. The second macro will fill 56 cells from the active cell cell downwards with the color for each index, which wil be shown in the cell to the right. (It is best to use 2 blank columns to run it, i.e. make sure the selected cell has no data underneath or to the right, for 56 rows)
    <pre>Sub ShowConditionalFontColors() <font color=448800>' return the 3 conditional font colors, 0 = not set</font color=448800>
    Dim X(2) As Integer
    On Error Resume Next
    For i = 0 To 2
    X(i) = Selection.FormatConditions(i + 1).Font.ColorIndex
    Next i
    MsgBox ("Conditional Font = " & X(0) & ", " & X(1) & ", " & X(2))
    End Sub</pre>

    <pre>Sub ShowColors() '<font color=448800>Show colors of existing palette with Index No </font color=448800>
    For i = 1 To 56
    ActiveCell.Offset(i - 1, 0).Interior.ColorIndex = i
    ActiveCell.Offset(i - 1, 1).Value = i
    Next
    End Sub </pre>

    You could use the FormatConditions(x).Font property in a macro that might want to sum conditionally colored cells, but I would recommend a conditional sum using the same conditions that applied in the formatting condition, if at all possible.

    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Conditionally Formatted Colors

    Andrew:
    Thank you much for the showcolor macros. It is just what a color blind guy like me needed. Now I can assign the conditonal color formats and get their count.
    Stephen

Posting Permissions

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