CODEWITHSUNDEEP
rexcelfilter
Mint
Mint

Reputation: 21

How can I extract all the cell values (as well as their cell IDs) that have a specific color?

I have a dataset which looks like this.

enter image description here

I want to create another dataset which only has two columns:

(1) The value/text of all cells that are red (2) The original cell ID associated with each value/text

Essentially, it should look like this.

enter image description here

I am assuming that there is an easy way to do this, but for some reason I haven't been able to make it work on excel (maybe it is because I am using the online version of excel, but I am unsure). I would also be happy to do this using R , in case that is easier.

Thank you in advance!

p.s. Sorry about the weirdly cropped dataset (done for privacy).

Upvotes: 2

Views: 138

Answers (1)

Lucas
Lucas

Reputation: 675

Since you are willing to use VBA and asked for further advise, I wrote the code below to serve as a guide so you can learn, change it and achieve the desired result.

Assuming you have a sheet named 'Colored data' like this:

enter image description here

And a sheet named 'Analysis' like this:

enter image description here

You can run this code:

Sub CompareAndHighlightDifferences()

Dim w1 As Worksheet, w2 As Worksheet
Dim cell_color As String
Dim cell_address As String
Dim new_row As Long

Set w1 = Sheets("Colored data")
Set w2 = Sheets("Analysis")

With w1
    For Each cel In .UsedRange
        cell_color = cel.Interior.Color
        cell_address = cel.Address
        new_row = w2.Cells(Rows.Count, 1).End(xlUp).Row + 1
        w2.Cells(new_row, 1) = cell_address
        With w2.Cells(new_row, 2)
            .Value = cell_color
            .Interior.Color = cell_color
        End With
    Next cel
End With

End Sub

And achieve the following results in the sheet 'Analysis':

enter image description here

Hope that helps.

Upvotes: 2

Related Questions