MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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
End If
End Sub


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.


OzGrid Business Applications