# Return interior fill color

Posted by Annette on February 28, 2001 7:00 PM

This was a follow-up a ways down - I copied it up to reply... See my reply following the text below:

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

Yay! We're almost there! Thank you so much! It worked on the rows that are one solid color all the way across. We're tripping up by checking the whole row. Can we check just one field in a row using something such as "B(i)" to indicate which cell to reference?
The problem is that columns 'I' and 'Q' have other colors for notation of special problems. The color index I need to return is always the same for columns A thru H.

P.S. Do you prefer flowers, chocolate, or coffee? :-)

Posted by David Hawley on February 28, 2001 7:20 PM

Hi Annette

We can use this one, it will check cell B in each row . Is this what you mean ?

Sub CheckRows()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 2500
Select Case Rows(i).Cells(1, 2).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

Chocolates,flowers or coffee ? Hmmmm, let me think.....nah don't worry, how about you sign my guest book on my web site instead!

Dave.

Posted by Annette on March 01, 2001 7:02 PM

Cool! I really should go get a VBA book....
Thank you!!!
And - I would love to sign the guestbook if I could find it - I don't see a link on the home page....................
Thank you again!!

Posted by David Hawley on March 01, 2001 10:10 PM

Annette.

I think you have gone to Mr.Excels home page. Mine is OzGrid Business Applications. Click the link at the bottom of this message.