PDA

View Full Version : Possible to apply conditional formatting across multiple ranges?



plasteredric
11-22-2017, 12:14 PM
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.

21017

paulked
11-23-2017, 12:29 PM
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.

21035

Cheers

Paul Ked

paulked
11-23-2017, 12:35 PM
X Posted here: https://www.excelforum.com/excel-formulas-and-functions/1209785-possible-to-apply-conditional-formatting-across-multiple-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
11-23-2017, 03:47 PM
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.

21035

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
11-23-2017, 04:20 PM
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
11-25-2017, 03:28 AM
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




21047

Cheers

Paul Ked