Seamus2626
asked on
Loop and highlight
Hi,
I need a piece of code that counts Col A as its reference for how many rows to loop through and then starts looping down.
If it finds that there are rows which have the same value in both columns F&G, then it will highlight those rows red
I have attached a spreadsheet which shows what i would like the ss to look like after the code has ran
Thanks
Seamus
test.xls
I need a piece of code that counts Col A as its reference for how many rows to loop through and then starts looping down.
If it finds that there are rows which have the same value in both columns F&G, then it will highlight those rows red
I have attached a spreadsheet which shows what i would like the ss to look like after the code has ran
Thanks
Seamus
test.xls
ASKER
Yep!
Seamus,
My mistake - I didn't check your file adequately. I had thought you were looking to highlight individual rows where columns F and G were the same.
You're actual requirements can be done by Conditional Formatting, but I'd be concerned that there would be an excessive processing overhead. How many rows will you have and how volatile will the data be?
Finally, by counting column A as the reference, do you mean to only include rows where column A is non-blank?
Thanks,
Brian.
My mistake - I didn't check your file adequately. I had thought you were looking to highlight individual rows where columns F and G were the same.
You're actual requirements can be done by Conditional Formatting, but I'd be concerned that there would be an excessive processing overhead. How many rows will you have and how volatile will the data be?
Finally, by counting column A as the reference, do you mean to only include rows where column A is non-blank?
Thanks,
Brian.
ASKER
There could easily be a few hundered rows of data
Yes, if Column A ends at row 75, only loop as far as 75
Thanks
Seamus
Yes, if Column A ends at row 75, only loop as far as 75
Thanks
Seamus
Seamus,
OK, quick and dirty...
Brian.test-V2.xls
OK, quick and dirty...
Option Explicit
Sub HighLight_Duplicates()
Dim i As Long
Dim xLast_Row As Long
Dim xLast_Col As Long
Dim xCount
Sheets("Sheet1").Activate
xLast_Col = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Column
xLast_Row = ActiveSheet.Range("A1").End(xlDown).Row
If xLast_Row < 2 Then
MsgBox ("No data found. Run cancelled.")
Exit Sub
End If
Range(Cells(2, 1), Cells(xLast_Row, xLast_Col)).Interior.Color = 16777215
Range(Cells(2, xLast_Col + 1), Cells(xLast_Row, xLast_Col + 1)).Formula = "=F2&"":""&G2"
Range(Cells(2, xLast_Col + 2), Cells(xLast_Row, xLast_Col + 2)).Formula = "=IF(A2="""","""",COUNTIF($M$2:$M$" & xLast_Row & ",M2))"
For i = 2 To xLast_Row
If Cells(i, xLast_Col + 2) <> 1 Then Range(Cells(i, 1), Cells(i, xLast_Col)).Interior.Color = 255
Next
Range(Cells(2, xLast_Col + 1), Cells(2, xLast_Col + 2)).EntireColumn.Delete
End Sub
Regards,Brian.test-V2.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats perfect Brian
Thanks
Seamus
Thanks
Seamus
Thanks, Seamus.
Hi,
Can someone explain this?
Range(Cells(2, 1), Cells(ActiveSheet.Range("A 1").Specia lCells(xlL astCell).R ow, xLast_Col)).Interior.Color = 16777215
&
Range(Cells(2, xLast_Col + 1), Cells(2, xLast_Col + 2)).EntireColumn.Delete
Why do you need above lines?
Thanks,
Ashok
Can someone explain this?
Range(Cells(2, 1), Cells(ActiveSheet.Range("A
&
Range(Cells(2, xLast_Col + 1), Cells(2, xLast_Col + 2)).EntireColumn.Delete
Why do you need above lines?
Thanks,
Ashok
Hi, Ashok.
(1) This is to remove any existing highlighting as the user may wish to re-run the macro after changing/deleting some rows.
(2) The main work is done by the formulas in the two new columns. Once the formatting has been applied they're no longer needed and so they're deleted.
Regards,
Brian.
(1) This is to remove any existing highlighting as the user may wish to re-run the macro after changing/deleting some rows.
(2) The main work is done by the formulas in the two new columns. Once the formatting has been applied they're no longer needed and so they're deleted.
Regards,
Brian.
Brian,
Now it makes sense.
Thank you
Ashok
Now it makes sense.
Thank you
Ashok
This could be done with Conditional Formatting. Beside simplicity, the main advantage is that the highlighting would be automatically updated if either of the values changed,
Interested?
Regards,
Brian.