VBA Excel - Find Coloured Cell Return Values

[vaneagle]

Hi,

I am trying to improve on a manual process. I am new to VBA and use Excel 97. I need to find a cell that is formatted with ColorIndex = 19. It then needs to return that cell and some cell values in the same row but different columns...
Here is an example of how the data would appear:

Col A Col B Col C Col D Col E Col F
1 aaa bbb ccc 111 222 333
2 aab bbc ccd 444 555 666
3 aac bbd cce 777 888 123
4 aad bbe ccf 456 789 234
5 aae bbf ccg 878 657 221
6 aaf bbg cch 543 543 777

I am using bold, italics, underline, and the colour red (as it stands out better than yellow), to highlight the cells that would have a yellow format.

What I need is to find the yellow cell, and return its contents as well as the contents on the same row, which are in columns a b & c.

An example of the result, which would sit in a different sheet:

Col A Col B Col C Col D
1 aab bbc ccd 666
2 aaf bbg cch 777

Difficulty (if it isn't already!!):

The source data would be a moving target as the user uses filters or sorting. So I am guessing that you would maybe need to find the row and column reference of the yellow cell, before copying the other columns in the same row. I am guessing here....

Thanking you in advance.....

 

[vaneagle]

Having played with some code i can't seem to grasp what is wrong...

Sub CellCoordinates()
        
    Sheets("Stores copy").Select
    Range("p2").Select
    For Each c In Range("p2:AA1000")
       If c.Interior.ColorIndex = 19 Then
        'how do i show the row and column reference??
        Else
        ActiveCell.Offset(1, 0).Select
       End If
    Next
End Sub

Firstly I don't think it is picking up the yellow cell, and secondly if and when it does, how do I get the column and row reference ??

 

[TonyJollans]

Hi vaneagle,

I'm not well up on colours so I stand to be corrected but ...

I think .Interior.[whatever] gets you the Cell colour and ...
.Font.[whatever] gets you Font colour.

From what I understand, .ColorIndex checks against the colour palette which can be changed independently and if you want an absolute colour you should check .Color.

Lastly, when you've found what you want, to get the row and column you use, obviously when you know, c.Row and c.Column.

Enjoy,
Tony

 

[vaneagle]

Hi Tony,

I have solved part of my problem... wrong colour code number!!!
I should have used 36... the code is slightly out as well...

I looked post at Excel VBA: Clear all cells with a certain interior color

So my initial code has changed...courtesy of the above thread..

Sub find()
Range("a1").Select

Const Light_Yellow = 36

With Sheets("Sheet1")
    For Each c In .Range("a1:e10") 'change to be your range
        If c.Interior.ColorIndex = Light_Yellow Then
            c.Row
            c.Column
        Else
        End If
    Next
End With

End Sub

Apologies for my lack of vba knowledge... I am in the early learning phase... i error at c.row do I need to dimension the row and column variable ??


Regards,

Paul

 

[vaneagle]

Thanks for helping to clarify things Tony.
Ahh figured it out...

Code is now:

Sub find()

Dim nCol As String
Dim nRow As String

Range("a1").Select
Const Light_Yellow = 36
With Sheets("Sheet1")
    For Each c In .Range("a1:a10")
        If c.Interior.ColorIndex = Light_Yellow Then
        nCol = c.Column
        nRow = c.Row
        
        Result = msgbox("The cell is at col " & nCol & " and row " & nRow, , "It Works!!")
       
        Else
        End If
    Next
End With

End Sub

I can go on now..
Paul