Reporting back values of cells with certain formats

[TomH]

I have a mass of cells, all of which contain numeric values. A subset of those cells have a certain format, specifically a fill. Is there a way of identifying the values in those cells with the specific fill and reporting them out in a list in another area of the spreadsheet?

Thanks for any help!

 

[Mihail]

This code will find the cells that are filled with color 255 (red)

Sub FindCells()
Dim LookingForColor As Long
    LookingForColor = 255
Dim R As Long, C As Long 'Rows Columns
    For R = 1 To 1000 'From Row1 to Row1000
        For C = 1 To 100 'From Column1 to Column100 in every row
            If Cells(R, C).Interior.Color = LookingForColor Then
                MsgBox (R & " , " & C)
            End If
        Next C
    Next R
End Sub

 

[TomH]

Hi Mihail.

Thanks. Your code does find the red cells, but the report-back is a series of message boxes with the numeric values of the rows and columns showing where the red cells are. What I need is a listing of the cell values of the red cells.

Is it possible to convert, in the code, the R and C values in each loop to an address and get the cell value from that? Then, I'd just have to build a report section in the spreadsheet and find a way to put the first (C,R) loop's value in the first cell of that section, the second in the second, etc.

Thanks again for your help.

 

[Brianwarnock]

Why not write the values to a Results sheet

Add dim RR as long
RR =1

Before entering the loops then replace the msg box row with

Sheets("results").cells(RR, 1).value = cells(r, c).value
RR=RR+1


Brian

 

[Mihail]

Yes. Brian's code will do the trick.
I am not sure about the .Value .
My approach is a little bit simplified:

Sheets("results").Cells(RR, 1) = Cells(r, c)

but is the same.

 

[Brianwarnock]

.Value is the default, I included it for clarity

Brian

 

[TomH]

Hi guys.

I want the output to show in a sheet called Handout, in column AP, starting in row 1. Tried the following. Works GREAT! Thanks again.

Sub FindCells()

Dim LookingForColor As Long
LookingForColor = 255
Dim RR As Long
RR = 1

Columns("AP:AP").Select
Selection.ClearContents

Dim R As Long, C As Long
For R = 1 To 25
For C = 1 To 40
If Cells(R, C).Interior.Color = LookingForColor Then
Sheets("Handout").Cells(RR, 42).Value = Cells(R, C).Value
RR = RR + 1
End If
Next C
Next R

End Sub

One last question... if I want to use a color other than red (255) how do I find the numeric value for the color? THANKS!

 

[Mihail]

Good for you and glad to help you.

One more thing.
Use the R1C1 view for your Excel. How to set this ? That is depending of your Excel version.
Google a little bit and you will find "how to".
Believe me: As a programmer you will love this style.
Good luck !

 

[Brianwarnock]

Tom
You can use a function to find out the numeric value of colours

Say you have a colour in a1
Then
Msgbox Range("A1").interior.color

Will return the value

Brian

 

[Brianwarnock]

What I would do is add the following code to your sub


Lookingforcolor= Activecell.interior.color

Instead of a hard coded value

Then select from your spreadsheet the color you want and run the code


Brian