Sorting and subtotalling by interior fill color
Posted by Annette on February 26, 2001 10:13 PM
What syntax is required to get a return of the interior color index number?
such as if fillcolor=17 then 17
I have a color-coded dataset. The rows are color-coded. If I could just apply the interior color index number to a separate column, I could handle the sort/subtotal myself.
Posted by David Hawley on February 27, 2001 3:39 AM
Hi Annette
You will only be able to achieve this via VBA in which case the code would be:
Sub GetColour()
If Range("A1").Interior.ColorIndex = 5 Then
'You code
Else
'Whatever
End If
End Sub
Dave
OzGrid Business Applications
Posted by Annette on February 27, 2001 10:34 PM
Ok I have this so far:
Sub GetColor()
If Range("A*").Interior.ColorIndex = 50 Then A1 = 50
If Range("A*").Interior.ColorIndex = 10 Then A1 = 10
If Range("A*").Interior.ColorIndex = 42 Then A1 = 42
If Range("A*").Interior.ColorIndex = 40 Then A1 = 40
If Range("A*").Interior.ColorIndex = 35 Then A1 = 35
End Sub
Thank you for your help! I really don't know Visual Basic and have spent a few hours on the web elsewhere trying to find the answer to this - unsucessfully! So here I am again - please help.
What do I do about that pesky little asterisk??? I stuck it in there to represent 'check the current row,' then I need to repeat this going down 2500 rows.
Thank you again!!
Posted by David Hawley on February 28, 2001 12:27 AM
Hmmm, I'm not this is what you want, but this code will check the first 2500 Rows of the active sheet. If the entire row has a color index as stated then it will put the Color index number into the first cell of that row.
Sub CheckRows()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 2500
Select Case Rows(i).Interior.ColorIndex
Case 50
Rows(i).Cells(1, 1) = 50
Case 10
Rows(i).Cells(1, 1) = 10
Case 42
Rows(i).Cells(1, 1) = 42
Case 40
Rows(i).Cells(1, 1) = 40
Case 35
Rows(i).Cells(1, 1) = 35
End Select
Next i
Application.ScreenUpdating = True
End Sub
Any Good ?
BTW it is only by chance I scrolled down this far. It is usually a good idea to re-post your question after 24 hours.
Dave
OzGrid Business Applications
