0

I am using Excel-2010, I have applied 3scale conditional formatting for Excel range A1:F100, on a Dropdown macro hide/unhide will be actioned, and hide/unhide is not sequential, for eg:- Rows 1,10,30,54,67,88 may only visible.

So for only these visible rows, the same conditional format should Work.

I have tried browsing to find it, but i couldn't get the required.

Help is much Appreciated.

Thanks in advance.

Community
  • 1
  • 1
Punith GP
  • 690
  • 4
  • 11
  • 33
  • I think you have to programmatically do it. Like changing the range where the formatting applies to. [See this to know about applying formatting to a range of cells programmatically](http://stackoverflow.com/questions/22500483/conditional-formatting-using-3-conditions-in-macro-vba/22501780#22501780) – L42 Sep 04 '14 at 09:28
  • If you could accept AVERAGE instead of MEDIAN (50%-PERCENTILE) for the Midpoint, then there is a possibility with SUBTOTAL formulas for Minimum, Midpoint and Maximum. – Axel Richter Sep 04 '14 at 09:54

2 Answers2

1

As commented, it should be something like this:

Sub ject()
    Dim rng As Range
    With Sheet1 '~~> change to your actual sheet
        .Range("A1:F100").FormatConditions.Delete
        Set rng = .Range("A1:F100").SpecialCells(xlCellTypeVisible)
        .Range("A1").FormatConditions.AddColorScale 3
        With .Range("A1").FormatConditions(1)
            With .ColorScaleCriteria(1)
                .Type = xlConditionValueLowestValue
                .FormatColor.Color = RGB(255, 0, 0)
            End With
            With .ColorScaleCriteria(2)
                .Type = xlConditionValuePercentile
                .FormatColor.Color = RGB(255, 255, 0)
            End With
            With .ColorScaleCriteria(3)
                .Type = xlConditionValueHighestValue
                .FormatColor.Color = RGB(0, 255, 0)
            End With
            .ModifyAppliesToRange rng
        End With
    End With
End Sub

Everytime this routine is run or called, it re-applies formatting to visible ranges.
It can be incorporated to an existing code or run separately. HTH.

L42
  • 19,427
  • 11
  • 44
  • 68
  • :-) Great, Thanks L42, This become like ready code, Changed only color codes. Much Appreciated. – Punith GP Sep 04 '14 at 10:40
  • @expfresh np. glad it helped and hope helps more readers. I receive a lot of help myself in this site. I'm just giving back if I can. – L42 Sep 05 '14 at 00:22
1

There is a possibility with SUBTOTAL formulas for Minimum, Midpoint and Maximum.

Minimum: Type = Formula, =SUBTOTAL(105,$A$1:$F$100)

Midpoint: Type = Formula, =MEDIAN(IF((SUBTOTAL(103,INDIRECT("A"&ROW($1:$100)))>0)*($A$1:$F$100<>""),$A$1:$F$100))

Maximum: Type = Formula, =SUBTOTAL(104,$A$1:$F$100)

If you could accept the average of the values instead the 50% percentile as the Midpoint, the formula for Midpoint would be simpler:

=SUBTOTAL(101,$A$1:$F$100)

Greetings

Axel

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • 2
    In Excel 2010 you can use `AGGREGATE` function to ignore hidden rows, and which has an inbuilt option for `Median`, e.g. `=AGGREGATE(12,1,A1:F100)` – barry houdini Sep 04 '14 at 11:24