Avatar of tesla764
tesla764 asked on

Help with vba Columns and rows

Problem Statement:
Check column by column to see if Column(x) Row4 is Interior Color is Gray color RGB(220, 230, 241)
 If YES
Check all rows in Column(x) for content
If there is content, copy the content to Column(x), Row 2

Non Duplicates...
Check to see if the currently read contents are the same as the previously read contents
If they are not the same (No Duplicates)
      Write the contents to Cell Column (x), Row 2

excel vba check column for value
excel vba check column for duplicates

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Example using the screen shot in the document…
1)      Column “H” Row “4” contains the characters “CT_001” in a cell that has a gray background RGB(220,230,241)
2)      Check (read) the next row in Column “H”. It contains the characters “No”.
3)      Write the Characters “No” into a “read” variable.
4)      Write that variable to Column “H” Row “2”
5)      Read the next row. In this case “No Response Data Found”
6)      Check to see if the characters are equal to the “read” variable.
7)      If they are not write the characters to Column “H” Row “2”
8)      Move the characters “No Response Data Found” to the ‘Read” variable ( want to ensure duplicates do not get written.
9)      In this case Column “H” Row “2” will now contain the Characters “No” and “No Response Data Found”
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Code so far…
Sub FindGray

Dim col As Range, rg As Range
    Application.ScreenUpdating = False
    Set rg = ActiveSheet.UsedRange

If rg.Column <> 1 Then Range(Cells(1, 1), Cells(1, rg.Column - 1)).EntireColumn.Hidden = True
Range(Cells(1, rg.Column + rg.Columns.Count), Cells(1, ActiveSheet.Columns.Count)).EntireColumn.Hidden = True

For Each col In ActiveSheet.UsedRange.Columns
    If col.EntireColumn.Cells(4, 1).Interior.Color <> RGB(220, 230, 241) Then _ 
Next

End Sub

Open in new window

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Any help I can get with this would be greatly appreciated.
Please let me if you need more details or information.
Thanks in advance.

Please see the attached word file for problem re-statement and screenshot examples. Your help with this would be greatly appreciated.

Thanks in advance.
EE-Aggregate.docx
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
tesla764

8/22/2022 - Mon
Hamed Nasr

Upload a sheet with this issue. Show the results expected in the sheet.
ASKER
tesla764

Please see attached file "EE-Aggregate.docx'.
Martin Liss

I'm not sure I can help but if I were to try I'd need your workbook.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Faustulus

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
tesla764

Thank You. I have incorporated this into my program and with some adaptation this works very well. Thanks again for all your help.