Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

Re: Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

Interesting...

why isnt C8 coloured Green?

Does it have to be conditional formatting? Or just normal formatting?

I guess you DONT need to summary of the sets, just colour in the sets?

Ger

Re: Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

Here is an inefficient brute force method which will capture up to 6 sets / levels with a gap of 3. On a small matrix it should be fine, but I wouldnt run this on 1000x1000 matrix - you might have to go for a cup of tea in that case.

Option Explicit
Const GAP = 3
Const COLOURS = "14974150,15189684,10086143,8696052,9359529,12566463"


Public Sub Compare_Cells()


Dim r As Range
Dim rCell As Range
Dim lCol As Long
Dim lRow As Long
Dim rSets(5) As Range
Dim iloop As Integer


On Error Resume Next
Set r = Application.InputBox("Please select a Range", "Range", , , , , , 8)
On Error GoTo 0
If Not r Is Nothing Then
    For Each rCell In r.Cells
        For lRow = 1 To r.Rows.Count
            For lCol = 1 To r.Columns.Count
                If rCell.Address <> r(lRow, lCol).Address Then
                    If Abs((rCell.Value) - (r(lRow, lCol).Value)) <= GAP Then
                        Debug.Print rCell.Address & " " & r(lRow, lCol).Address
                        For iloop = LBound(rSets) To UBound(rSets)
                            If rSets(iloop) Is Nothing Then
                                Set rSets(iloop) = Union(rCell, r(lRow, lCol))
                                Exit For
                            Else
                                If Not Intersect(rSets(iloop), rCell) Is Nothing Then
                                    Set rSets(iloop) = Union(rSets(iloop), rCell, r(lRow, lCol))
                                    Exit For
                                ElseIf Not Intersect(rSets(iloop), r(lRow, lCol)) Is Nothing Then
                                    Set rSets(iloop) = Union(rSets(iloop), rCell, r(lRow, lCol))
                                    Exit For
                                End If
                            End If
                        Next
                    End If
                End If
            Next
        Next
    Next
End If


On Error Resume Next
For iloop = LBound(rSets) To UBound(rSets)
    rSets(iloop).Interior.Color = Split(COLOURS, ",")(iloop)
Next
End Sub

Seems to work... Conditional formatting is NOT possible AFAIK.

Ger

Re: Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

Quote

Kindly have a look at A9 (188) and B9(192) this has gap/factor of 4 points, so either one of A9 (188) or B9(192 ) should have been ignored. Nonetheless, this could be due to comparison of it with B8 (189) and hence both got selected.

Your guess is correct... its because of the comparison with B8 (189) that brought it within the 3 point gap. In my head that would be right The code sort of creates a linked list (or chain) of cells that are within three of each other. For example - really simple example - with just three cells on the row... values are 3, 6 and 9 - the gap between 3 and 6 is just 3 so that will be highlighted, but also in the same way the gap between 6 and 9 is also 3, so that will be highlighted too in the same chained list of cells. I'm not sure what logic you would consistently employ to say that 3 and 6 are linked by a gap of three, but seperately 6 and 9 are also linked by a gap of three. Since 6 is common to both, you cant have one cell with two colours at the same time, so they end up getting the same colours as they are part of that link / chain. But I understand that 3 to 9 is a gap of 6 and therefore breaks the Gap rule with those figures. But I'm stuck. Basically in my example 3 and 6 would be one colour, and 6 and 9 would be another colour... but six cant be both colours

Quote

if i want to tweak the code ... with gap /factor of only 2 instead of 3 currently
When i changed this to 2,

Correct - to change the GAP, update the CONST GAP numbers from 3 to 2 (or increase if you wish). Well spotted

Quote

if i want to tweak the code with only 2 scenario / level

For example for just two scenarios, change rsets(5) to rsets(1) ... why one? Because the index here starts at 0, so rSets(0) and rSets(1) will give two scenarios. It will beasically ignore all other groups/chains and find (from the top left cell in the selection) only two scenarios and ignore other possible scenarios.
rSets(5) is actually giving you 6 scenarios. If you want more than 6 scenarios, you will need to update the CONST Colours statement as each number between the comma's is a colour, and I have only defined 6 colours So you might get an error, or a black fill for anything more than 6 scenarios, until you add more to that CONST Colours line at the top.

Regards
Ger

Re: Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

:cool: