Possible to apply conditional formatting across multiple ranges?

[plasteredric]

I have several ranges of data, each range is identically sized as they are slightly different calculations on the same data. I would like to apply conditional formatting to highlight the highest and lowest values between the cells at the same relative position in each range.

I have been able to do it with two ranges using the following formulas '=AND(ISNUMBER(B4),B4<B14)' & '=AND(ISNUMBER(B4),B4>B14)'
but not sure how I would go about it with 3 or more ranges.

Any help appreciated.

con form ex.xlsx

[paulked]

Conditional formatting will do up to 3 colours easily:

Select a Rule Type: Format all cells based on their values
Format Style: 3-Color Scale
and then select the three cells to compare.

con form ex ked.xlsx

Cheers

Paul Ked

[paulked]

X Posted here: https://www.excelforum.com/excel-for...le-ranges.html

Please follow the rules, it's not very nice to find out you may be working on a solution that has already been found!

[plasteredric]

Conditional formatting will do up to 3 colours easily:

Select a Rule Type: Format all cells based on their values
Format Style: 3-Color Scale
and then select the three cells to compare.

con form ex ked.xlsx

Cheers

Paul Ked

I assume with that method I would require a separate rule for each cell in the range? so in the example I would have 48 separate rules. Is there a way of incorporating it all into one? im not sure how the multiple rules will impact on the performance

[paulked]

I'm in the pub now! Multiples can be done but l would have to try over the weekend.

48 CF's is nothing! It is only looking for an answer when that particular group of cells is changed so there would be no performance issues.

[paulked]

Put this in Sheet1 Object:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim rw As Long, cl As Long, r As Long, c As String
    If Not Intersect(Target, Range("B4:G11,B14:G21,B24:G31")) Is Nothing Then
        For cl = 2 To 7
            If cl = 2 Then c = "B"
            If cl = 3 Then c = "C"
            If cl = 4 Then c = "D"
            If cl = 5 Then c = "E"
            If cl = 6 Then c = "F"
            If cl = 7 Then c = "G"
            For r = 0 To 30 Step 10
                For rw = 4 To 11
                    If Cells(rw + r, cl) = WorksheetFunction.Large(Range(c & rw & ", " & c & rw + 10 & " ," & c & rw + 20), 1) _
                          Then Cells(rw + r, cl).Interior.Color = 255
                    If Cells(rw + r, cl) = WorksheetFunction.Large(Range(c & rw & ", " & c & rw + 10 & " ," & c & rw + 20), 2) _
                          Then Cells(rw + r, cl).Interior.Color = 65535
                    If Cells(rw + r, cl) = WorksheetFunction.Large(Range(c & rw & ", " & c & rw + 10 & " ," & c & rw + 20), 3) _
                          Then Cells(rw + r, cl).Interior.Color = 5287936
                Next
            Next
        Next
    End If
    Application.ScreenUpdating = True
End Sub

con form ex ked.xlsm

Cheers

Paul Ked