Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Conditional formatting (Excel 2003)

    Hi all....I am attaching a little sample that I need some help with....cell K4 counts the number of records (based on EMPLOYEE #) posted to the Overtime record (1 record per line).....Hans has helped me with a formula that will conditionally format K4 if an employee # is posted more than once (to prevent duplicates).....with 225 employees, this will require scanning the whole document looking for the duplicate...I would like to conditionally format the duplicate employee # in column D if it posted more than once.....any suggestions, b/c the cond format formula that works in K4 doesn't seem to work if I modify it and apply it to D10227...thank you....sample attached.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    The attached sheet has a conditional format that will highlight the actual duplicates...
    Attached Files Attached Files
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting (Excel 2003)

    Rudi has already answered your question. The formula I created for you earlier calculates whether there are duplicates at all, so it can't be used directly in conditional formatting for individual cells. As you can see from Rudi's solution, though, the basic idea remains the same.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    Thank you Rudi...works great

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    Thanks, Hans....can I ask a question about VBA...I am trying to make a VBA code that will Save a workbook and the Close it....all I can see are codes for Save, or Close...but nothing for Exit....is Close the only 'piece' of code that does what I want? And, I seem to have a lot of modules in my spredsheet...each time I make a macro, it seems to create a module...is this good or bad? If bad, how can I move or copy the modules into a single one and remove the other 'empty'ones?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting (Excel 2003)

    If you want to close and save the active workbook at the same time:
    <code>
    ActiveWorkbook.Close SaveChanges:=True
    </code>
    You can replace ActiveWorkbook with a reference to a specific open workbook, e.g. Workbooks("Overtime.xls").

    To quit Excel after saving all workbooks:
    <code>
    Application.Quit
    </code>
    You can copy/paste (or cut/paste) the text of your macros into a single module, then select a superfluous module in the project explorer and select File | Remove <modulename>... You'll be asked whether you want to export the module before removing it, answer No. Repeat until you've removed all superfluous modules.

    (I generally create a new module by starting to write code below the last one in a module, i.e. by reusing an existing module. I only use Insert | Module when I want to create an entirely new module)

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    Thank you....do the various macros have to be put into the single module in any order?....I envision just opening the last module and then copying/pasting each of them into it...I notice that a line is drawn across under the End Sub line and I gather that keeps the various macros spearate...and why do I want to do this? Will it make my workbook run faster or smaller or something??

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting (Excel 2003)

    There is no need to keep the macros in any particular order, except for readability.

    If the number of macros within one workbook becomes very large, I'd distribute them over several modules, grouped by purpose/functionality. This makes it easier to maintain the code. If you have only a few macros, there is no need to place them in different modules.

    The separator line between procedures (macros) is optional - you can turn it on or off in Tools | Options in the Visual Basic Editor. This is a matter of personal preference. It has no influence on performance.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    ???...???...I copied all macros into 1 module, double checked to make sure they're all present...all were...now, when I hit CRTL + C (to run a macro) nothng happens...it says "Select destimation and press ENER or choose paste".....??..??...it was working perfectly until I combined everything, not it doesn't.....the individual macros seem to run ok, but I have a DoBoth that calls 4 different macros in sequence...??

    I am wondering if it somethign to do with a Save and Close macro....b/c that refers to the ActiveWorkbook and the Month end report is the last workbook to get something pasted into it, so could Excel be confused about which is the "Active"workbook?

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    Just to answer another question you asked: "And, I seem to have a lot of modules in my spredsheet...each time I make a macro, it seems to create a module"

    If you stay within your session of Excel and record 3 macros, the code will all be together on one module. Each time you close and open Excel and record macros, the macro will be added to a new module. Then you can cut and paste the code if required to an individual module if you want to keep everything together as Hans mentioned.
    Regards,
    Rudi

  11. #11
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting (Excel 2003)

    CTRL + C is the standard Windows shortcut for COPY - are you sure that is what you meant?

    If you want to assign a shortcut, (in Excel 2002 and I would guess it is similar in 2003), Tools > Macro > Macros, select the macro you want o assign a shortcut to, and click Options.
    Attached Images Attached Images
    • File Type: gif x.gif (11.2 KB, 0 views)

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    If you have cut macros from different workbooks, then you will have a referencing problem as the code when recorded is specific to the workbook in which it was recorded. If all the macros were in the same workbook across multiple modules, you will not have the problem.

    PS: Just a tip...
    I find it better to use Uppercase letters when asigning Shortcut Keys to macros. When doing this it will not override any default shortcuts in Excel. For example: CTRL + SHIFT + C or stead of CTRL + C.
    Regards,
    Rudi

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Conditional formatting (Excel 2003)

    Thank you, Hans, Lief & Rudi.....I made a few changes and now it's all as smooth as silk....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •