macro for repeat task and format interior

Hi All,
I'm trying to check out repeated value on column Q and the hightlighted
entire cell on the left.I put the code that found from Sample,and trying
to modified it but I made a mistake.I can't select entire cell on the left,
I just can highlight 2 cell ( offset(0.-3) and the cell on column A).Please
help how to select entire cell to the left without interupted by blank cell.

And also I want to put the copy of higlighted cell value to a new workbook.

Thank's.

Rgds,


Shiro


Sub Duplicate_Serial_Number()

   Dim eX As Integer
   Dim cell_in_loop As Range

   eX = ActiveSheet.Evaluate("COUNTIF(Q:Q,"">1"")")

If eX = 0 Then
MsgBox "There is no duplicated serial number ", vbExclamation _
+ vbOKOnly, "No Duplicated Data"
Else
For Each cell_in_loop In Range("Q16:Q50000")
  If cell_in_loop.Value > 1 And _
  cell_in_loop.Value <> "" Then
    With cell_in_loop.Offset(0, -3).End(xlToLeft).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
  End If
Next
End If
End Sub



0
shiro (16)
5/20/2008 9:15:38 AM
excel 39879 articles. 2 followers. Follow

1 Replies
868 Views

Similar Articles

[PageSpeed] 41

One way:

Option Explicit
Sub Duplicate_Serial_Number()

    Dim myCell As Range
    Dim LastRow As Long
    Dim myRng As Range
    Dim myRngToShade As Range
    
    With ActiveSheet
        'clean up any previous shading
        .Cells.Interior.ColorIndex = xlNone
        LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
        Set myRng = .Range("Q16:Q" & LastRow)

        If Application.Max(myRng) < 2 Then
            MsgBox "There is no duplicated serial number ", vbExclamation _
                + vbOKOnly, "No Duplicated Data"
        Else
            For Each myCell In myRng.Cells
                If myCell.Value > 1 Then
                    Set myRngToShade = .Range(.Cells(myCell.Row, "A"), _
                                                  .Cells(myCell.Row, "N"))
                    With myRngToShade.Interior
                        .ColorIndex = 6
                        .Pattern = xlSolid
                    End With
                End If
            Next myCell
        End If
    End With
        
End Sub

=======
Colors are pretty, but I like to use that extra column (Q) and then use
data|Filter|autofilter to see the duplicates.  



shiro wrote:
> 
> Hi All,
> I'm trying to check out repeated value on column Q and the hightlighted
> entire cell on the left.I put the code that found from Sample,and trying
> to modified it but I made a mistake.I can't select entire cell on the left,
> I just can highlight 2 cell ( offset(0.-3) and the cell on column A).Please
> help how to select entire cell to the left without interupted by blank cell.
> 
> And also I want to put the copy of higlighted cell value to a new workbook.
> 
> Thank's.
> 
> Rgds,
> 
> Shiro
> 
> Sub Duplicate_Serial_Number()
> 
>    Dim eX As Integer
>    Dim cell_in_loop As Range
> 
>    eX = ActiveSheet.Evaluate("COUNTIF(Q:Q,"">1"")")
> 
> If eX = 0 Then
> MsgBox "There is no duplicated serial number ", vbExclamation _
> + vbOKOnly, "No Duplicated Data"
> Else
> For Each cell_in_loop In Range("Q16:Q50000")
>   If cell_in_loop.Value > 1 And _
>   cell_in_loop.Value <> "" Then
>     With cell_in_loop.Offset(0, -3).End(xlToLeft).Interior
>         .ColorIndex = 6
>         .Pattern = xlSolid
>     End With
>   End If
> Next
> End If
> End Sub

-- 

Dave Peterson
0
petersod (12004)
5/20/2008 11:37:58 AM
Reply:

Similar Artilces:

macro error message
I'm writing some code(in excel, visual basic). The following code is attached to the click action of a button. Private Sub cmdNewPayApp_Click() Sheets("Pay App").Visible = True Sheets("Pay App").Select Sheets("Pay App").Copy After:=Sheets(2) Sheets("Pay App (2)").Select Sheets("Pay App (2)").Name = "Pay App 1" Sheets("Pay App").Select ActiveWindow.SelectedSheets.Visible = False Sheets("Pay App 1").Select Range("O11").Select ActiveCell.Formula = "=A...

Macro to open macros
I need a macro to open multiple macros when it is ran. Any ideas ?(by the way, Im not very experienced with macros as you might be able to tell). Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Stu, See reply in .programming. If you want to hit multiple groups, do it all in one post, not multiple posts. It just helps everybody. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing di...

Lookup Macro
Using MS Word 2007 and Window's XP I had this macro written into one of my programs and it will lookup fields and replace the MAX, or Min Value. I need to convert it into Word. Can someone help me? Thank you. I have another post that also reads "Lookup Macro but forgot that I had this other program where I had already done this I just don't know how to convert it into Word. GLOBAL varMAX_A GLOBAL varMIN_A Sub GetMinMaxValueA() Dim EntryFlag As Boolean EntryFlag = False varMAX_A = 0 varMIN_A = 9999.99 If Application.activeform.Fields("LBSIN_A&quo...

named range in macro call 04-01-10
I have written a macro that I would like to have operate the same way on different data ranges. I tried creating a generic macro that accepted a range of data as an input parameter, then I created several buttons that each called the same macro, but with a different argument, as follows: Private Sub CommandButton1_Click() Call Macro1( "NamedRange1" ) End Sub Private Sub CommandButton2_Click() Call Macro1( "NamedRange2" ) End Sub and so on. However, I can't seem to find the right syntax for specifying a range name in the call to the macro (...

A Macro needed
Hi, I have a workbook with 10 worksheets. In column A2:A30, I have "Student Names". I have "Lesson Names" in Range B1:G1 which is constant and doesn't change from worksheet to worksheet. But range B2:G30 has different integer values in different worksheets. Now I need a macro, that when executed will create new worksheets with the unique Student Names from columns A:A in all sheets, and sum up all values from all worksheets for particular Lesson Grades from range B2:G30. To simplify, Say I have a name "Jack Junior" in A5 for Sheet1. The same name may appear in...

how do I format a cell using zero as the first number?
I am entering numbers in an excel spread sheet and some of my numbers startwith a zero, which does not show up. How can I change that? Well this is an Access database forum; however, since Access has the same behavior, I'll take a stab: Numbers don't have leading zeros. If you need to see leading zeros, format it as a text field. In Access there are some fancy ways to display leading zeros in front of numerical characters, but then it's no longer a number. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builde...

email with attachments keeps repeating to the recipient
when I send an email with an attached document or photo, wether it be initatyed by me or forwarded, the recipients gets the email multiple times. I have to delete the outgoing email to limit the number of times the recipient gets the email. thanks all. Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'd still be sufficiently protected by your on-access scanner part of the virus scanner. For more details see; http://www.msoutlook.info/question/20 -- Robert Sparnaaij [MVP-...

Excel macro
I'm trying to create a macro for a table where I want macro to prompt in cell A1 and after entering a number and pressing 'enter' I want cursor to move to cell B1 and prompt for another entry, and after entering and pressing 'enter' cursor should go to cell C1 and prompt . After entering and pressing 'enter' I want cursor to go to cell A2 and repeat as above steps. I tried all different ways but don't know how to have macro prompt? Any hints or help will be apprecieted. ------------------------------------------------ ~~ Message posted from http://www.ExcelTi...

Image cannot be displayed, macro problem
In Excel 2003 it worked....in Excel 2007 it doesn't. I have a macro in my main workbook that, among other things, opens an external workbook, copies an image from it, and pastes that image into the main workbook. It then closes the external workbook. The image successfully copies over--but as soon as the macro closes the external workbook, the perfectly displayed image in the main workbook suddenly transforms into the Red x "image cannot be displayed" message. Ideas!? ...

Hard to explain
Okay - this is really tough for me to explain but I have a code for my macro that I have an error on and I CANNOT figure out why! I am not too good at explaining this so please ask any questions on info you may need!!! Thank you so much in advance!!! Here is my code - please please help me! Sub Macro2() ' ' Macro2 Macro ' Macro recorded 5/2/2006 by Authorized User ' ' Keyboard Shortcut: Ctrl+z ' Sheets("Corp").Visible = True ActiveSheet.Select Range("A2:M2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ...

Yes/No Format Issue
Hi Groupies I have been building databases for years and have not run into this problem before. This 2007 database I am working in has a few yes/no fields. When I add these fields to a Form, they are show up as textboxes instead of the yes/no check box I am expecting. They respond correctly in queries, autoreports and autoforms but when I add it manually, I get Yes or No and have to manually change it to a Check Box Any ideas? Anybody else having the same issue? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself,...

Sorting by Format?
.....this may be a candidate for dumbest question of the year on this board but, here goes, - is it possible to sort by format features such as highlighting for instance ... i have a table with rows highlighted in different colors for certain reasons and need to organize them based on that highlighting, cant seem to figure out how to do that if its even possible? :confused: -- RalphSE ------------------------------------------------------------------------ RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931 View this thread: http://www.excelforum.co...

macro for sorting
I have a field for small management areas which are two letters or numbers. There are larger management areas that are made up of five or six of these smaller management areas, when I recieve the excel sheets I sort the smaller management areas into their larger ones manually. I was wondering if there was a way I could use some vba code to sort them into the larger groups automatically. Also does anyone know of a good web site to find excel vba code?? Thank you, Nicholas Cherry http://www.cpearson.com/excel.htm http://www.j-walk.com/ss/excel go to developer tips. -- Regards, To...

Macro for summarizing multiple sheets
Hello all I have a workbook with multiple sheets. Each sheet is identically laid out (the only difference is the sheet name - cart1, cart2, etc). Each sheet represent the contents of a cart and the expiration date of applicable items. I would like to create a macro or function that will search each sheet in sequence and return on a summary sheet the following information: the name of the sheet and any of the items with expiration dates that are within 45 day of the current date. Any help or suggestions would be much appreciated - see below for an example of the workbook ...

chart formatting #2
since excel already has conditional formatting for cells, it should also have conditional formatting for charts. like turning a portion of a line red if it falls, then the next blue if it rises. just a suggestion. Hi see: http://peltiertech.com/Excel/Charts/format.html#CondChart and http://peltiertech.com/Excel/Charts/format.html#CondChart2 -- Regards Frank Kabel Frankfurt, Germany "Pringles." <Pringles@discussions.microsoft.com> schrieb im Newsbeitrag news:C0975DF9-CBA2-41A2-BB20-0BA647A40846@microsoft.com... > since excel already has conditional formatting for cells...

Helps with Macros
I have a datafeed that comes to me everyday i need to format one column of arounf 1000 rows so it multiples the total column but a 1000 so getting rid of the decimal point i.e =A3100.00 becoming 10000 , i also need to do this using a macro. can anyone help One way of doing this manually is to find an empty helper cell, put 100 in that helper cell. Then copy that helper cell. Select your range to adjust. edit|paste special|and check the multiply operation. Then clean up that helper cell. In code, you could do the same thing like this: Option Explicit Sub testme() Dim myRng As Range ...

Displaying Worksheet Macro Button
Hi all, I have a worksheet that my boss and I will be sharing. Sometimes, we want to display the data as is, other times, we want to run a macro that will re-format it. I've got the macro all designed and functioning, but what I want to do now is to display a button for the macro on the toolbar, but somehow make it only show up when that one Workbook is opened, regardless of who's opening it (i.e., if I send the file to my boss and she opens it, she should see the button). Is that possible? If not, are there any other options you can suggest that would allow me to run a macro o...

Hide Macro Running
When I press the VBA button you can see it going through the process. How do I hide this process? Thanks Frank Hi Frank, I suppose you mean you can see what happens to the worksheets. In your macro, include "Application.ScreenUpdating = False" in the beginning and dito "True" towards the end. -- Kind regards, Niek Otten Microsoft MVP - Excel "FRANKB" <FBORGER@YAHOO.COM> wrote in message news:05b401c3820e$c7522bb0$a401280a@phx.gbl... > When I press the VBA button you can see it going through > the process. How do I hide this process? > >...

Macro to always take you to the last active row
Hi guys, Looking for some help... I need to create a Macro which will always take you to the last active row in a spreadsheet and then insert three new lines which will be formatted in the same manner as the line above. I'm creating a risks and issues log so the three lines are merged together for half of the columns and then not for the other half if this causes problems with the Macro. Is this possible? I've tried to do it by recording a macro but doesn't seem to work as when I insert the new lines they always appear on the same row....annoying. Hopefully you can hel...

Can't format font colors or cell fill-in colors
I cannot fill-in a cell with a color or format the color of the font. I verified that this I can change font color in Word. So it appears to only be happening in Excel. Already tried relaunching and rebooting computer. Anyother ideas or places to look where I may have changed a setting? -- rrl Does this happen in all workbooks, or just a single workbook? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "canoeron" <canoeron@discussions.microsoft.com> wrote in message news:CB278F8E-EF1F-4218-91F8-C100F1D30256@micros...

how I can format a number like hundreds, thoudsands
I am from INdia and need to format the numbers in Indian style like comma sepration based on hundreds, thousand pattern Very first hit on Google searching for Format and Indian:- From a Google search here is a reply by David Richie to the same question in January If you have Excel 2002 or later look up bhattext in help. Otherwise the following format might help you [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00 (This format separates groups for India/Thailand, format valid for positive numbers up to 99,99,99,999.99 or 999,999,999.99) in Excel XP see function Bh...

Two Macros-One Module
Your Team answered two questions with copy-able codes that I was able to get to work seperately. (1) Entering time without ":" or "AM/PM" and (2) a default to "PM" only upon entry/exit of cell - they're great! I never did a macro before and it is cool! Follwoing are the two macro codes so that you may tell me which lines to delete/change. MACRO-1 - - - - - - - - - - - - - - - Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("E8:F13,E18:F23")) Is Nothi...

Macro
Hi all, When running a macro i am getting a error as FIELD ON WINDOW IS INACTIVE OR DISABLED(Line #4) . please suggest me how to rectify that. Regards Vishy Vishy, You are probably running a macro that left the focus on a customer id or vendor id or item id that is now locked because you typed in the the id. Let me know if this is the case. -- Thanks, Matt Landis Landis Computer "If you think SBF can't do it, give us a call!" SBF Consulting, Training and HelpDesk Nationwide http://www.landiscomputer.com (717) 733-0793 x101 "rajesh" <rajesh.neela...

chart/worksheet/macro interaction
I am running a macro which simulates an engineering application. The macro essentially time steps, sending the data at each time step to a worksheet, and a chart is set up to display the data in graphical form. Problem - although the worksheet is updated at each time step, the chart only updates when the macro has finished executing - and I want to see the data being graphed as the simulation proceeds - is there anyway of getting the macro to pause while the chart is updated - some kind of handshake between EXCEL and the macro? (At the moment the only way I can do this is to put a STOP...

macros for text
Hello I have a spread sheet that contains six diferent peoples names in a schedule format throughout the workbook I would like to change each name to a different text color with out having to change each one. I would like a macro that would change the text of each name I select aand chage it in the entire wookbook. so if I have the name steve inthe schedule 70 times it will go through the spreadsheet and change every steve to a red text. (hope this makes sense -- Thank You Steve Have you considered using conditional formatting? You could set this up for one cell, and then copy the con...