troubleshooting Question

Help with vba Columns and rows

Avatar of tesla764
tesla764 asked on
Microsoft OfficeMicrosoft ExcelMicrosoft Applications
5 Comments1 Solution569 ViewsLast Modified:
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros