98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel
Excel
Resolved · High Priority · Version 2007
Wed 8 Sep 2010, 16:15Delegate John said...
John has attended:
No courses
Excel
Hi, I have a column of cells that i need to rearrange into a new column. I will be entering the serial numbers into the new column manually. What i would like to do is.
As i have typed the number into the cell and press enter, I would like excel to compare the number with all the numbers in the original column and if it finds the same number i would like the original number to be deleted or changed into a new colour or have the cell colour changed.
So at the end all i see in the original column is the cells which do not match any the cells in the new column.
I know this means that each cell in the new column has to be compared with the every cell in the original, but i am stuck on where to go from here.
Many thanks in advance for any help you can provide.
John
Mon 13 Sep 2010, 13:27Trainer Simon said...
RE: Excel
Hi John,
Thank you for your question.
I believe this problem would have to be resolved with a Visual Basic Macro.
It is difficult to attain your level of knowledge as you haven't completed a course with us.
I have included some vba code that can be inserted into the visual Basic window as a Worksheet_Change event.
http://en.kioskea.net/faq/4698-excel-comparing-cell-a1-to-entire-a-column-in-sheet-2
With the link above at the end you could replace the answers with formats.
http://en.kioskea.net/forum/affich-190666-format-cell-based-on-another-macro
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("N1") = "G" Then Range("K1").Font.ColorIndex = 4
If Range("N1") = "A" Then Range("K1").Font.ColorIndex = 44
If Range("N1") = "R" Then Range("K1").Font.ColorIndex = 3
If Range("N1") <> "G" And Range("N1") <> "A" And Range("N1") <> "R" Then Range("K1").Font.ColorIndex = 1
End Sub
There are some good forums out there to search for such requests and I think Mr.Excel is a good forum.
I hope this helps.
Regards
Simon
Fri 17 Sep 2010, 12:32Trainer Nafeesa said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Thu 23 Sep 2010: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Deleting a range of cells using the autofill handleFirstly, select the range of cells for which you would like to clear the contents. Then drag the autofill handle to the the top left corner of the selection whilst holding down the shift key. Your selected contents should then be deleted. |
