Hi all
I don't know about the rest of you but, there just hasn't been enough hours in a day lately ! It's been all work and no play :o( Oh well, that's life!
As the numbers are now increasing please feel free to post any questions, answers, tips and tricks etc. I personally may not get time to supply an answer to any questions, but there could be someone out there with the answer and we all like tips and tricks.
Since the last Update I have added two new pages to my Site, they are "Custom Formats" and "Cost of Services". Be sure to follow the Microsoft link on the "Custom Formats" page as it makes very interesting reading.
For this months tip a thought I would show you all a Custom Function (UDF) I wrote recently for one of my students. It is to enable you to sort by colour.
Function ColourRank(ColorOrder As Range, LookCell As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''
'Will list a referenced cell fom 1 to X
'''''''''''''''''''''''''''''''''''''''
Dim i As Integer
Dim ICol1 As Integer
Dim ICol2 As Integer
'Force recalculation
Application.Volatile
'Set variables
i = 1
ICol2 = -1
ColourRank = 0
'Loop until match is found
Do Until ICol1 = ICol2
ICol1 = ColorOrder(i, 1).Interior.ColorIndex
ICol2 = LookCell.Interior.ColorIndex
If i = ColorOrder.Rows.Count + 1 Then
'No Match found place in Text
ColourRank = "No colour match!!!"
Exit Do
End If
'Pass the Row number of the colour match
ColourRank = i
i = i + 1
Loop
End Function
- To see this in use, paste it in a normal Module
- Change the Fill colour of cells A1:A5 with 5 different colours.
- Now in cells D1:D5 put the same colours but in a *different* order
- Now select cell E1 and push Shift+F3 and scroll down to "User Defined" then select "ColourRank" and click OK.
- Now use this syntax:=ColourRank($A$1:$A$5,D1)
- Copy this down to D5.
- Now sort E1:D5 by Column "E"
Not sure if there would be a lot of use for this, but I thought it was an interesting request.
