Avatar of 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)
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 _ 

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.
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

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


Please see attached file "EE-Aggregate.docx'.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

I'm not sure I can help but if I were to try I'd need your workbook.
Avatar of Faustulus
Flag of Singapore image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of tesla764


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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo