VBA - Document particular cells value, address and heading to New Sheet

[Anbuselvam K]

Dear All

Find the attached sheet, In that, I want to find the yellow-coloured cells and it must be documented to the new sheet with its value and cell address.

For that, I tried below code and I got error 438

Option Explicit

Sub SelectColoredCells()
    Dim Sh As Worksheet
    Dim rCell As Range, PDate As Range
    Dim lColor As Long, r As Long
    Dim rColored As Range
    Dim High As Date
    
    Application.ScreenUpdating = False
    ' Create a new sheet
    Set Sh = Worksheets.Add
    lColor = RGB(255, 255, 0)

    Set rColored = Nothing
    For Each rCell In Sheet9.Range("A7:BD800")
        If rCell.Interior.Color = lColor Then
            If rColored Is Nothing Then
                Set rColored = rCell
            Else
                Set rColored = Union(rColored, rCell)
            End If
        End If
    Next
If rColored Is Nothing Then
        MsgBox "No cells match the color"
    Else
 With Sh
        'Put header "Comment", "Address" & "Author" in A1, B1 & C1 respectively.
        .Cells(1, 1).Value = "Value"
        .Cells(1, 2).Value = "Cell Address"
    r = 2
        For Each rColored In Sheet9
            .Cells(r, 1).Value = rColored.Value
            .Cells(r, 2).Value = rColored.Parent.Address
            r = r + 1
        Next rColored
        .Columns.AutoFit
End With
End If
End Sub

Also, I want to add the column heading name for each yellow highlighted cells in the new sheet.

Please do the needful.

Thanks in advance.

 

[Marc L]

Hi,​

​

as you forgot to attach the expected result workbook with a filled layout, so erase your code and​

restart from the faster code I already gave you last September for exactly the same yellow cells search …​

​

 

[Anbuselvam K]

In the attached sheet 2 have the expected results. With your earlier code, I can find the yellow highlighted cells and then I do not know how to modify the codes to get the cell values, heading and address to the other new sheet.

Also, in your earlier code, you were using dictionary and other symbols which i couldn't understand as I am new to VBA.

Expecting your valuable reply.

 

[Marc L]

you were using dictionary

Wrong, just Excel Basics at beginner level like when you operate manually, all is yet in the VBA inner help …​

​

 

[Anbuselvam K]

Dear Marc L

Thanks for your suggestion and your valuable time spent towards my question.

Let me wait for others help as I really can't find with the VBA inner help to build the code.

 

[Marc L]

If you wish to continue with your initial code,​

as the message explains clearly the issue so just check the codeline which fires the error …​

​

 

[Anbuselvam K]

Got the corrected code.

https://www.mrexcel.com/board/threa...nd-heading-to-new-sheet.1118757/#post-5393777

 

[vletm]

Anbuselvam K
Please reread Forum Rules: https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
Steps are written in this order:

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.