Question about Highlighting a Row

[Jakboi]

Hello,

Here is the situation. I have a spreadsheet given to me each week that has say 300-500 rows of data. I then have to take that data and skim out the rows that are not necessary.

I guess here is my question.

If some the rows were highlighted say yellow, is it possible via a macro or something to delete all rows that were not yellow. I then have to take what is left and compare it to another sheet and delete any duplicates for a quarter (3 months). I am trying to find a faster easier way of doing this.

Currently this is what happens:

I receive the paper copy of the spreadsheet with rows highlighted in which I take those and count the x number of rows for each occurance that happens in column y.

Column Y = Name of Employee
Row X= Client Contact Note

The rows that are highlighted are the occurances that the employee recieves credit for. I then have to take that list and they are only given credit if its a unique contact for the quarter. So I compare the 2 spreadsheets and what is left gets added to my report which counts thats employees unique contacts for the quarter.

So then I have to list each employee on another spreadsheet with their name

Anyone have any idea how I can make this easier?

I am figuring I can have the spreadsheet sent to me via attachment with the rows highlighted, and I want it delete the ones that are not and compare its self to another spread sheet and delete any duplicates...

Any direction...would this work better with Access than Excel...maybe if I import it and have some code that could do it all?

 

[Brianwarnock]

To answer your first question, yes you can delete by colourindex, the code below does that, select a cell containg the colour you want to delete the rows of , sorry for the contorted English, then run the macro. Obviously it will be easy to change this to not delete that colour but all others.

Haven't figured out how to do the rest, which looks like the hard part.

Brian

Sub deletebycolour()

'Brian Warnock 20/09/06

Dim lngCounter As Long
Dim lngLastRow As Long

ci = ActiveWindow.RangeSelection.Interior.ColorIndex

With ActiveSheet.UsedRange
lngLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With

lngCounter = 1

Application.ScreenUpdating = False
Do While lngCounter <= lngLastRow
Set r = Range("a1")
If r.Cells(lngCounter, 1).Interior.ColorIndex = ci Then
Rows(lngCounter).Delete
lngCounter = lngCounter
lngLastRow = lngLastRow - 1
Else
lngCounter = lngCounter + 1
End If

Loop
Application.ScreenUpdating = True
End Sub

 

[Jakboi]

Thanks Brian for the insight. I will give it a shot. Yea I didnt know if setting up Access database might work as well, unless thats waht you meant.

Then I could maybe import the sheet, have a macro or code delete the non highlighted rows then I could have that table compare its self to another and delete duplicate Client Names for a particular quarter or time frame.

See this is how this comes now field names wise...its a SQL from a database program used to store data our company purchased:


ACCT_NUMBER (this is the clients account number)
SHORT_ACCOUNT_TITLE (this is the clients name)
CONTACT_COMMENTS (Client contact comments)
CONTACT_TYPE_TEXT (type of contact)
ENTERED_BY (employee name)
INITIAL_CONTACT_DATE (intial contact date)
DATE_ENTERED (date entered into our system)

 

[Brianwarnock]

I think you replied as I was closing last night(uk).

I don't think the highlight will be carried with the import, so I would select out the highlight in EXCEL and then do the imports to ACCESS and work on deleting the duplicates.

Brian

 

[Jakboi]

I think you replied as I was closing last night(uk).

I don't think the highlight will be carried with the import, so I would select out the highlight in EXCEL and then do the imports to ACCESS and work on deleting the duplicates.

Brian

Yea I was just thinking the highlights probably wouldn't...I will see what I come up with and thanks for help.