Asc/Descending Cell Values.... (1 Viewer)

GUIDO22

Registered User.
Local time
Today, 21:46
Joined
Nov 2, 2003
Messages
515
I am after some VBA code to run or (conditional formatting if that is easier / also possible), where the same three adjacent cells per row are compared to one another.....

Each cell has a numerical value - but I wish to only highlight those rows where the cell values are in Ascending (or Descending) order...

Would appreciate some help from anyone that may know the best way to do this please... thank you.

exmaple:
If the order filter is set DESCENDING

Row #1 = 34 ,65 ,23
Row#2 = 56 , 49 , 45
Row #3 = 32 , 40 , 45

Row#2 is the only qualifier in this case...

Note : I only have use of Office 2007

TIA
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:46
Joined
Sep 21, 2011
Messages
14,047
What happens if you have


56, 56, 49 ?
 

GUIDO22

Registered User.
Local time
Today, 21:46
Joined
Nov 2, 2003
Messages
515
.. good question.. but in this case, its not of interest ie. void
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:46
Joined
Sep 21, 2011
Messages
14,047
Ok,
Here is something to start with.
It expects you to highlight the first column of values and supply whether ascending or descending.
As it is only 3 columns, I hard coded it, but if anything more I would have worked out some sort of loop.
Comment out the debug print when it is working as it should. I've only carried out basic testing BTW


HTH

Code:
Sub Check_Values()
Dim rngCell As Range
Dim lngValue As Long, lngRow As Long, lngLast As Long
Dim blnGood As Boolean
Dim intLoop As Integer, intStart As Integer, intEnd As Integer
Dim strDirection As String, strCol As String
strDirection = UCase(InputBox("(A)scending or (D)escending?", "Select sort direction", "A"))
If strDirection <> "A" And strDirection <> "D" Then
    MsgBox "Invalid selection - " & strDirection
    Exit Sub
End If
strCol = Split(Columns(ActiveCell.Column).Address(, False), ":")(1)
lngLast = Range(strCol & Rows.Count).End(xlUp).Row
For Each rngCell In Range(strCol & "1:" & strCol & lngLast)
    blnGood = False
    rngCell.Select
    If strDirection = "D" Then
        If (ActiveCell.Value > ActiveCell.Offset(0, 1).Value) And (ActiveCell.Offset(0, 1).Value > ActiveCell.Offset(0, 2).Value) Then
            blnGood = True
        End If
    Else
        If (ActiveCell.Value < ActiveCell.Offset(0, 1).Value) And (ActiveCell.Offset(0, 1).Value < ActiveCell.Offset(0, 2).Value) Then
            blnGood = True
        End If
    End If
    If blnGood Then
        lngRow = Selection.Row
        With Rows(lngRow).Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
        Debug.Print ActiveCell.Offset(0, 0).Value & " " & ActiveCell.Offset(0, 1).Value & " " & ActiveCell.Offset(0, 2)

    End If
Next
End Sub
 
Last edited:

GUIDO22

Registered User.
Local time
Today, 21:46
Joined
Nov 2, 2003
Messages
515
.. thanks, but I wish to paste a table of values into the spreadsheet and have this filtering applied automatically....
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:46
Joined
Sep 21, 2011
Messages
14,047
.. thanks, but I wish to paste a table of values into the spreadsheet and have this filtering applied automatically....

Well you are on your own there.
I did not just knock that code up from experience. I had to go looking on the net to get bits and pieces. All I had was an idea as how to go about it and a little bit of a method I have used in the past.

I expect you would need to put it in a workbook event somewhere, but how are you ment to indicate which column is the first and whether you want ascending or descending?


You post stated 'Would appreciate some help from anyone that may know the best way to do this please... thank you.'

I have tried to do that. It might not be the best way, but that is the way I would approach it.
 

GUIDO22

Registered User.
Local time
Today, 21:46
Joined
Nov 2, 2003
Messages
515
Sheet 2 of the worksheet could have two variables...

One var would be an identifier for the column that represents the first value to compare the others against...
ie. ColD/E/F... in my setup F would alwyas be the first value.

The second var. would be an integer to represent Asc/Desc

That way I dont have to select a value for each row that is processed...

On sheet 1 I would paste the table, the code would run in accordance with the Var settings on sheet2...

(I am familiar with Access VBA but not so much with Excel Worksheets...)
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:46
Joined
Sep 21, 2011
Messages
14,047
Well if it will always be column F, you can hardcode it to strCol which is where I worked out which column was selected.
Then you only need to indicate Asc or Desc, I did that with the input box, you could do it by assigning the variable to Sheets("Sheet2").Range("A1").Value where the value is in cell A1 and amend the test accordingly. Personally I think a 1 or a 2 is not as descriptive as "A" or "D" or "Asc" or "Desc", but that is your choice.

If you decide that the first colum might change from F then do likewise for the strCol variable.


I still do not know which event would be best though.



HTH
 

Users who are viewing this thread

Top Bottom